Macros for SAS Application Developers
https://github.com/sasjs/core
mp_loadformat.sas
Go to the documentation of this file.
1/**
2 @file
3 @brief Loads a format catalog from a staging dataset
4 @details When loading staged data, it is common to receive only the records
5 that have actually changed. However, when loading a format catalog, if
6 records are missing they are presumed to be no longer required.
7
8 This macro will augment a staging dataset with other records from the same
9 format, to prevent loss of data - UNLESS the input dataset contains a marker
10 column, specifying that a particular row needs to be deleted (`delete_col=`).
11
12 This macro can also be used to identify which records would be (or were)
13 considered new, modified or deleted (`loadtarget=`) by creating the following
14 tables:
15
16 @li work.outds_add
17 @li work.outds_del
18 @li work.outds_mod
19
20 For example usage, see mp_loadformat.test.sas
21
22 @param [in] libcat The format catalog to be loaded
23 @param [in] libds The staging table to load
24 @param [in] loadtarget= (NO) Set to YES to actually load the target catalog
25 @param [in] delete_col= (_____DELETE__THIS__RECORD_____) The column used to
26 mark a record for deletion. Values should be "Yes" or "No".
27 @param [out] auditlibds= (0) For change tracking, set to the libds of an audit
28 table as defined in mddl_dc_difftable.sas
29 @param [in] locklibds= (0) For multi-user (parallel) situations, set to the
30 libds of the DC lock table as defined in the mddl_dc_locktable.sas macro.
31 @param [out] outds_add= (0) Set a libds here to see the new records added
32 @param [out] outds_del= (0) Set a libds here to see the records deleted
33 @param [out] outds_mod= (0) Set a libds here to see the modified records
34 @param [in] mdebug= (0) Set to 1 to enable DEBUG messages and preserve outputs
35
36 <h4> SAS Macros </h4>
37 @li mddl_sas_cntlout.sas
38 @li mf_getuniquename.sas
39 @li mf_nobs.sas
40 @li mp_abort.sas
41 @li mp_cntlout.sas
42 @li mp_lockanytable.sas
43 @li mp_storediffs.sas
44
45 <h4> Related Macros </h4>
46 @li mddl_dc_difftable.sas
47 @li mddl_dc_locktable.sas
48 @li mp_loadformat.test.sas
49 @li mp_lockanytable.sas
50 @li mp_stackdiffs.sas
51
52
53 @version 9.2
54 @author Allan Bowe
55
56**/
57
58%macro mp_loadformat(libcat,libds
59 ,loadtarget=NO
60 ,auditlibds=0
61 ,locklibds=0
62 ,delete_col=_____DELETE__THIS__RECORD_____
63 ,outds_add=0
64 ,outds_del=0
65 ,outds_mod=0
66 ,mdebug=0
67);
68/* set up local macro variables and temporary tables (with a prefix) */
69%local err msg prefix dslist i var fmtlist ibufsize;
70%let dslist=base_fmts template inlibds ds1 stagedata storediffs;
71%if &outds_add=0 %then %let dslist=&dslist outds_add;
72%if &outds_del=0 %then %let dslist=&dslist outds_del;
73%if &outds_mod=0 %then %let dslist=&dslist outds_mod;
74%let prefix=%substr(%mf_getuniquename(),1,21);
75%do i=1 %to %sysfunc(countw(&dslist));
76 %let var=%scan(&dslist,&i);
77 %local &var;
78 %let &var=%upcase(&prefix._&var);
79%end;
80
81/*
82format values can be up to 32767 wide. SQL joins on such a wide column can
83cause buffer issues. Update ibufsize and reset at the end.
84*/
85%let ibufsize=%sysfunc(getoption(ibufsize));
86options ibufsize=32767 ;
87
88/* in DC, format catalogs maybe specified in the libds with a -FC extension */
89%let libcat=%scan(&libcat,1,-);
90
91/* perform input validations */
92%let err=0;
93%let msg=0;
94data _null_;
95 if _n_=1 then putlog "&sysmacroname entry vars:";
96 set sashelp.vmacro;
97 where scope="&sysmacroname";
98 value=upcase(value);
99 if &mdebug=0 then put name '=' value;
100 if name=:'LOAD' and value not in ('YES','NO') then do;
101 call symputx('msg',"invalid value for "!!name!!":"!!value);
102 call symputx('err',1);
103 stop;
104 end;
105 else if name='LIBCAT' then do;
106 if exist(value,'CATALOG') le 0 then do;
107 call symputx('msg',"Unable to open catalog: "!!value);
108 call symputx('err',1);
109 stop;
110 end;
111 end;
112 else if name='LIBDS' then do;
113 if exist(value) le 0 then do;
114 call symputx('msg',"Unable to open staging table: "!!value);
115 call symputx('err',1);
116 stop;
117 end;
118 end;
119 else if (name=:'OUTDS' or name in ('DELETE_COL','LOCKLIBDS','AUDITLIBDS'))
120 and missing(value) then do;
121 call symputx('msg',"missing value in var: "!!name);
122 call symputx('err',1);
123 stop;
124 end;
125run;
126
127%mp_abort(
128 iftrue=(&err ne 0)
129 ,mac=&sysmacroname
130 ,msg=%str(&msg)
131)
132
133/**
134 * First, extract only relevant formats from the catalog
135 */
136proc sql noprint;
137select distinct upcase(fmtname) into: fmtlist separated by ' ' from &libds;
138
139%mp_cntlout(libcat=&libcat,fmtlist=&fmtlist,cntlout=&base_fmts)
140
141
142/**
143 * Ensure input table and base_formats have consistent lengths and types
144 */
145%mddl_sas_cntlout(libds=&template)
146data &inlibds;
147 length &delete_col $3;
148 if 0 then set &template;
149 set &libds;
150 if &delete_col='' then &delete_col='No';
151 fmtname=upcase(fmtname);
152 if missing(type) then do;
153 if substr(fmtname,1,1)='$' then type='C';
154 else type='N';
155 end;
156 if type='N' then do;
157 start=cats(start);
158 end=cats(end);
159 end;
160run;
161
162/**
163 * Identify new records
164 */
165proc sql;
166create table &outds_add(drop=&delete_col) as
167 select a.*
168 from &inlibds a
169 left join &base_fmts b
170 on a.fmtname=b.fmtname
171 and a.start=b.start
172 where b.fmtname is null
173 and upcase(a.&delete_col) ne "YES"
174 order by fmtname, start;;
175
176/**
177 * Identify deleted records
178 */
179create table &outds_del(drop=&delete_col) as
180 select a.*
181 from &inlibds a
182 inner join &base_fmts b
183 on a.fmtname=b.fmtname
184 and a.start=b.start
185 where upcase(a.&delete_col)="YES"
186 order by fmtname, start;
187
188/**
189 * Identify modified records
190 */
191create table &outds_mod (drop=&delete_col) as
192 select a.*
193 from &inlibds a
194 inner join &base_fmts b
195 on a.fmtname=b.fmtname
196 and a.start=b.start
197 where upcase(a.&delete_col) ne "YES"
198 order by fmtname, start;
199
200options ibufsize=&ibufsize;
201
202%mp_abort(
203 iftrue=(&syscc ne 0)
204 ,mac=&sysmacroname
205 ,msg=%str(SYSCC=&syscc prior to load prep)
206)
207
208%if &loadtarget=YES %then %do;
209 data &ds1;
210 merge &base_fmts(in=base)
211 &outds_mod(in=mod)
212 &outds_add(in=add)
213 &outds_del(in=del);
214 if not del and not mod;
215 by fmtname start;
216 run;
217 data &stagedata;
218 set &ds1 &outds_mod;
219 run;
220 proc sort;
221 by fmtname start;
222 run;
223%end;
224/* mp abort needs to run outside of conditional blocks */
225%mp_abort(
226 iftrue=(&syscc ne 0)
227 ,mac=&sysmacroname
228 ,msg=%str(SYSCC=&syscc prior to actual load)
229)
230%if &loadtarget=YES %then %do;
231 %if %mf_nobs(&stagedata)=0 %then %do;
232 %put There are no changes to load in &libcat!;
233 %return;
234 %end;
235 %if &locklibds ne 0 %then %do;
236 /* prevent parallel updates */
237 %mp_lockanytable(LOCK
238 ,lib=%scan(&libcat,1,.)
239 ,ds=%scan(&libcat,2,.)-FC
240 ,ref=MP_LOADFORMAT commencing format load
241 ,ctl_ds=&locklibds
242 )
243 %end;
244 /* do the actual load */
245 proc format lib=&libcat cntlin=&stagedata;
246 run;
247 %if &locklibds ne 0 %then %do;
248 /* unlock the table */
249 %mp_lockanytable(UNLOCK
250 ,lib=%scan(&libcat,1,.)
251 ,ds=%scan(&libcat,2,.)-FC
252 ,ref=MP_LOADFORMAT completed format load
253 ,ctl_ds=&locklibds
254 )
255 %end;
256 /* track the changes */
257 %if &auditlibds ne 0 %then %do;
258 %if &locklibds ne 0 %then %do;
259 %mp_lockanytable(LOCK
260 ,lib=%scan(&auditlibds,1,.)
261 ,ds=%scan(&auditlibds,2,.)
262 ,ref=MP_LOADFORMAT commencing audit table load
263 ,ctl_ds=&locklibds
264 )
265 %end;
266
267 %mp_storediffs(&libcat-FC
268 ,&base_fmts
269 ,FMTNAME START
270 ,delds=&outds_del
271 ,modds=&outds_mod
272 ,appds=&outds_add
273 ,outds=&storediffs
274 ,mdebug=&mdebug
275 )
276
277 proc append base=&auditlibds data=&storediffs;
278 run;
279
280 %if &locklibds ne 0 %then %do;
281 %mp_lockanytable(UNLOCK
282 ,lib=%scan(&auditlibds,1,.)
283 ,ds=%scan(&auditlibds,2,.)
284 ,ref=MP_LOADFORMAT commencing audit table load
285 ,ctl_ds=&locklibds
286 )
287 %end;
288 %end;
289%end;
290%mp_abort(
291 iftrue=(&syscc ne 0)
292 ,mac=&sysmacroname
293 ,msg=%str(SYSCC=&syscc after load)
294)
295
296%if &mdebug=0 %then %do;
297 proc datasets lib=work;
298 delete &prefix:;
299 run;
300 %put &sysmacroname exit vars:;
301 %put _local_;
302%end;
303%mend mp_loadformat;