CORE
mp_ds2cards.sas
Go to the documentation of this file.
1 
31 %macro mp_ds2cards(base_ds=, tgt_ds= , cards_file= ,maxobs=max);
32  %local i;
33 
34 %if not %sysfunc(exist(&base_ds)) %then %do;
35  %put WARNING: &base_ds does not exist;
36  %return;
37 %end;
38 
39 %if %index(&base_ds,.)=0 %then %let base_ds=WORK.&base_ds;
40 %if (&tgt_ds = ) %then %let tgt_ds=&base_ds;
41 %if %index(&tgt_ds,.)=0 %then %let tgt_ds=WORK.%scan(&base_ds,2,.);
42 
43 %let nvars=%mf_getvarcount(&base_ds);
44 %if &nvars=0 %then %do;
45  %put WARNING: Dataset &base_ds has no variables! It will not be converted.;
46  %return;
47 %end;
48 
49 %if &maxobs = max %then %let maxobs=%sysfunc(getoption(obs));
50 
51 proc sql;
52 create table datalines1 as
53  select name,type,length,varnum,format,label from dictionary.columns
54  where libname="%upcase(%scan(&base_ds,1))"
55  and memname="%upcase(%scan(&base_ds,2))";
56 
67 data datalines_2;
68  format dataline $32000.;
69  set datalines1 (where=(upcase(name) not in
70  ('PROCESSED_DTTM','VALID_FROM_DTTM','VALID_TO_DTTM')));
71  if type='num' then dataline=
72  cats('ifc(int(',name,')=',name,'
73  ,put(',name,',best32.)
74  ,substrn(put(',name,',bestd32.),1
75  ,findc(put(',name,',bestd32.),"0","TBK")))');
76  else dataline=cats('strip(',name,')') ;
77 run;
78 
79 proc sql noprint;
80 select dataline into: datalines separated by ',' from datalines_2;
81 
82 %local
83  process_dttm_flg
84  valid_from_dttm_flg
85  valid_to_dttm_flg
86 ;
87 %let process_dttm_flg = N;
88 %let valid_from_dttm_flg = N;
89 %let valid_to_dttm_flg = N;
90 data _null_;
91  set datalines1 ;
92 /* build attrib statement */
93  if type='char' then type2='$';
94  if strip(format) ne '' then format2=cats('format=',format);
95  if strip(label) ne '' then label2=cats('label=',quote(trim(label)));
96  str1=catx(' ',(put(name,$33.)||'length=')
97  ,put(cats(type2,length),$7.)||format2,label2);
98 
99 
100 /* Build input statement */
101  if type='char' then type3=':$char.';
102  str2=put(name,$33.)||type3;
103 
104 
105  if(upcase(name) = "PROCESSED_DTTM") then
106  call symputx("process_dttm_flg", "Y", "L");
107  if(upcase(name) = "VALID_FROM_DTTM") then
108  call symputx("valid_from_dttm_flg", "Y", "L");
109  if(upcase(name) = "VALID_TO_DTTM") then
110  call symputx("valid_to_dttm_flg", "Y", "L");
111 
112 
113  call symputx(cats("attrib_stmt_", put(_N_, 8.)), str1, "L");
114  call symputx(cats("input_stmt_", put(_N_, 8.))
115  , ifc(upcase(name) not in
116  ('PROCESSED_DTTM','VALID_FROM_DTTM','VALID_TO_DTTM'), str2, ""), "L");
117 run;
118 
119 data _null_;
120  file &cards_file. lrecl=32767 termstr=nl;
121  length attrib $32767;
122  if _n_=1 then do;
123  put '/*******************************************************************';
124  put " Datalines for %upcase(%scan(&base_ds,2)) dataset ";
125  put " Generated by %nrstr(%%)mp_ds2cards()";
126  put " Licensed under GNU, available on github.com/macropeople/macrocore";
127  put '********************************************************************/';
128  put "data &tgt_ds ;";
129  put "attrib ";
130  %do i = 1 %to &nvars;
131  attrib=symget("attrib_stmt_&i");
132  put attrib;
133  %end;
134  put ";";
135 
136  %if &process_dttm_flg. eq Y %then %do;
137  put 'retain PROCESSED_DTTM %sysfunc(datetime());';
138  %end;
139  %if &valid_from_dttm_flg. eq Y %then %do;
140  put 'retain VALID_FROM_DTTM &low_date;';
141  %end;
142  %if &valid_to_dttm_flg. eq Y %then %do;
143  put 'retain VALID_TO_DTTM &high_date;';
144  %end;
145  if nobs=0 then do;
146  put 'call missing(of _all_);/* avoid uninitialised notes */';
147  put 'stop;';
148  put 'run;';
149  end;
150  else do;
151  put "infile cards dsd delimiter=',';";
152  put "input ";
153  %do i = 1 %to &nvars.;
154  %if(%length(&&input_stmt_&i..)) %then
155  put " &&input_stmt_&i..";
156  ;
157  %end;
158  put ";";
159  put "datalines4;";
160  end;
161  end;
162  set &base_ds end=lastobs nobs=nobs;
163 /* remove all formats for write purposes - some have long underlying decimals */
164  format _numeric_ best30.29;
165  length dataline $32767;
166  dataline=catq('cqsm',&datalines);
167  put dataline;
168  if lastobs or _n_ ge &maxobs then do;
169  put ';;;;';
170  put 'run;';
171  stop;
172  end;
173 run;
174 
175 %put NOTE: CARDS FILE SAVED IN:;
176 %put NOTE-;%put NOTE-;
177 %put NOTE- %sysfunc(dequote(&cards_file.));
178 %put NOTE-;%put NOTE-;
179 %mend;