Macros for SAS Application Developers
https://github.com/sasjs/core
Loading...
Searching...
No Matches
mp_applyformats.sas
Go to the documentation of this file.
1/**
2 @file
3 @brief Apply a set of formats to a table
4 @details Applies a set of formats to the metadata of one or more SAS datasets.
5 Can be used to migrate formats from one table to another. The input table
6 must contain the following columns:
7
8 @li lib - the libref of the table to be updated
9 @li ds - the dataset to be updated
10 @li var - the variable to be updated
11 @li fmt - the format to apply. Missing or default ($CHAR, 8.) formats are
12 ignored.
13
14 The macro will abort in the following scenarios:
15
16 @li Libref not assigned
17 @li Dataset does not exist
18 @li Input table contains null or invalid values
19
20 Example usage:
21
22 data work.example;
23 set sashelp.prdsale;
24 format _all_ clear;
25 run;
26
27 %mp_getcols(sashelp.prdsale,outds=work.cols)
28
29 data work.cols2;
30 set work.cols;
31 lib='WORK';
32 ds='EXAMPLE';
33 var=name;
34 fmt=format;
35 keep lib ds var fmt;
36 run;
37
38 %mp_applyformats(work.cols2)
39
40 @param [in] inds The input dataset containing the formats to apply (and where
41 to apply them). Example structure:
42 |LIB:$8.|DS:$32.|VAR:$32.|FMT:$49.|
43 |---|---|---|---|
44 |`WORK `|`EXAMPLE `|`ACTUAL `|`DOLLAR12.2 `|
45 |`WORK `|`EXAMPLE `|`COUNTRY `|`$CHAR10. `|
46 |`WORK `|`EXAMPLE `|`DIVISION `|`$CHAR10. `|
47 |`WORK `|`EXAMPLE `|`MONTH `|`MONNAME3. `|
48 |`WORK `|`EXAMPLE `|`PREDICT `|`DOLLAR12.2 `|
49 |`WORK `|`EXAMPLE `|`PRODTYPE `|`$CHAR10. `|
50 |`WORK `|`EXAMPLE `|`PRODUCT `|`$CHAR10. `|
51 |`WORK `|`EXAMPLE `|`QUARTER `|`8. `|
52 |`WORK `|`EXAMPLE `|`REGION `|`$CHAR10. `|
53 |`WORK `|`EXAMPLE `|`YEAR `|`8. `|
54
55 @param [out] errds= (0) Provide a libds reference here to export the
56 error messages to a table. In this case, they will not be printed to the
57 log.
58
59 <h4> SAS Macros </h4>
60 @li mf_getengine.sas
61 @li mf_getuniquefileref.sas
62 @li mf_getuniquename.sas
63 @li mf_nobs.sas
64 @li mp_validatecol.sas
65
66
67 <h4> Related Macros </h4>
68 @li mp_getformats.sas
69
70 @version 9.2
71 @author Allan Bowe
72
73**/
74
75%macro mp_applyformats(inds,errds=0
76)/*/STORE SOURCE*/;
77%local outds liblist i engine lib msg ;
78
79/**
80 * Validations
81 */
82proc sort data=&inds;
83 by lib ds var fmt;
84run;
85
86%if &errds=0 %then %let outds=%mf_getuniquename(prefix=mp_applyformats);
87%else %let outds=&errds;
88
89data &outds;
90 set &inds;
91 where fmt not in ('','.', '$', '$CHAR.','8.');
92 length msg $128;
93 by lib ds var fmt;
94 if libref(lib) ne 0 then do;
95 msg=catx(' ','libref',lib,'is not assigned!');
96 %if &errds=0 %then %do;
97 putlog 'ERR' +(-1) "OR: " msg;
98 %end;
99 output;
100 return;
101 end;
102 if exist(cats(lib,'.',ds)) ne 1 then do;
103 msg=catx(' ','libds',lib,'.',ds,'does not exist!');
104 %if &errds=0 %then %do;
105 putlog 'ERR' +(-1) "OR: " msg;
106 %end;
107 output;
108 return;
109 end;
110 %mp_validatecol(fmt,FORMAT,is_fmt)
111 if is_fmt=0 then do;
112 msg=catx(' ','format',fmt,'on libds',lib,'.',ds,'.',var,'is not valid!');
113 %if &errds=0 %then %do;
114 putlog 'ERR' +(-1) "OR: " msg;
115 %end;
116 output;
117 return;
118 end;
119
120 if first.ds then do;
121 retain dsid;
122 dsid=open(cats(lib,'.',ds));
123 if dsid=0 then do;
124 msg=catx(' ','libds',lib,'.',ds,' could not be opened!');
125 %if &errds=0 %then %do;
126 putlog 'ERR' +(-1) "OR: " msg;
127 %end;
128 output;
129 return;
130 end;
131 if varnum(dsid,var)<1 then do;
132 msg=catx(' ','Variable',lib,'.',ds,'.',var,' was not found!');
133 %if &errds=0 %then %do;
134 putlog 'ERR' +(-1) "OR: " msg;
135 %end;
136 output;
137 end;
138 end;
139 if last.ds then rc=close(dsid);
140run;
141
142proc sql noprint;
143select distinct lib into: liblist separated by ' ' from &inds;
144%put &=liblist;
145%if %length(&liblist)>0 %then %do i=1 %to %sysfunc(countw(&liblist));
146 %let lib=%scan(&liblist,1);
147 %let engine=%mf_getengine(&lib);
148 %if &engine ne V9 and &engine ne BASE %then %do;
149 %let msg=&lib has &engine engine - formats cannot be applied;
150 insert into &outds set lib="&lib",ds="_all_",var="_all", msg="&msg" ;
151 %if &errds=0 %then %put %str(ERR)OR: &msg;
152 %end;
153%end;
154quit;
155
156%if %mf_nobs(&outds)>0 %then %return;
157
158/**
159 * Validations complete - now apply the actual formats!
160 */
161%let fref=%mf_getuniquefileref();
162data _null_;
163 set &inds;
164 by lib ds var fmt;
165 where fmt not in ('','.', '$', '$CHAR.','8.');
166 file &fref;
167 if first.lib then put 'proc datasets nolist lib=' lib ';';
168 if first.ds then put ' modify ' ds ';';
169 put ' format ' var fmt ';';
170 if last.ds then put ' run;';
171 if last.lib then put 'quit;';
172run;
173
174%inc &fref/source2;
175
176%if &errds=0 %then %do;
177 proc sql;
178 drop table &outds;
179%end;
180
181%mend mp_applyformats;