Macros for SAS Application Developers
https://github.com/sasjs/core
mp_ds2inserts.sas
Go to the documentation of this file.
1 /**
2  @file
3  @brief Export a dataset to SQL insert statements
4  @details Converts dataset values to SQL insert statements for use across
5  multiple database types.
6 
7  Usage:
8 
9  %mp_ds2inserts(sashelp.class,outref=myref,outds=class)
10  data class;
11  set sashelp.class;
12  stop;
13  proc sql;
14  %inc myref;
15 
16  @param [in] ds The dataset to be exported
17  @param [in] maxobs= (max) The max number of inserts to create
18  @param [out] outref= (0) The output fileref. If it does not exist, it is
19  created. If it does exist, new records are APPENDED.
20  @param [out] schema= (0) The library (or schema) in which the target table is
21  located. If not provided, is ignored.
22  @param [out] outds= (0) The output table to load. If not provided, will
23  default to the table in the &ds parameter.
24  @param [in] flavour= (SAS) The SQL flavour to be applied to the output. Valid
25  options:
26  @li SAS (default) - suitable for regular proc sql
27  @li PGSQL - Used for Postgres databases
28  @param [in] applydttm= (YES) If YES, any columns using datetime formats will
29  be converted to native DB datetime literals
30 
31  <h4> SAS Macros </h4>
32  @li mf_existfileref.sas
33  @li mf_getvarcount.sas
34  @li mf_getvarformat.sas
35  @li mf_getvarlist.sas
36  @li mf_getvartype.sas
37 
38  @version 9.2
39  @author Allan Bowe (credit mjsq)
40 **/
41 
42 %macro mp_ds2inserts(ds, outref=0,schema=0,outds=0,flavour=SAS,maxobs=max
43  ,applydttm=YES
44 )/*/STORE SOURCE*/;
45 
46 %if not %sysfunc(exist(&ds)) %then %do;
47  %put %str(WAR)NING: &ds does not exist;
48  %return;
49 %end;
50 
51 %if not %sysfunc(exist(&ds)) %then %do;
52  %put %str(WAR)NING: &ds does not exist;
53  %return;
54 %end;
55 
56 %if %index(&ds,.)=0 %then %let ds=WORK.&ds;
57 
58 %let flavour=%upcase(&flavour);
59 %if &flavour ne SAS and &flavour ne PGSQL %then %do;
60  %put %str(WAR)NING: &flavour is not supported;
61  %return;
62 %end;
63 
64 %if &outref=0 %then %do;
65  %put %str(WAR)NING: Please provide a fileref;
66  %return;
67 %end;
68 %if %mf_existfileref(&outref)=0 %then %do;
69  filename &outref temp lrecl=66000;
70 %end;
71 
72 %if &schema=0 %then %let schema=;
73 %else %let schema=&schema..;
74 
75 %if &outds=0 %then %let outds=%scan(&ds,2,.);
76 
77 %local nobs;
78 proc sql noprint;
79 select count(*) into: nobs TRIMMED from &ds;
80 %if &nobs=0 %then %do;
81  data _null_;
82  file &outref mod;
83  put "/* No rows found in &ds */";
84  run;
85 %end;
86 
87 %local vars;
88 %let vars=%mf_getvarcount(&ds);
89 %if &vars=0 %then %do;
90  data _null_;
91  file &outref mod;
92  put "/* No columns found in &schema.&ds */";
93  run;
94  %return;
95 %end;
96 %else %if &vars>1600 and &flavour=PGSQL %then %do;
97  data _null_;
98  file &fref mod;
99  put "/* &schema.&ds contains &vars vars */";
100  put "/* Postgres cannot handle tables with over 1600 vars */";
101  put "/* No inserts will be generated for this table */";
102  run;
103  %return;
104 %end;
105 
106 %local varlist varlistcomma;
107 %let varlist=%mf_getvarlist(&ds);
108 %let varlistcomma=%mf_getvarlist(&ds,dlm=%str(,),quote=double);
109 
110 /* next, export data */
111 data _null_;
112  file &outref mod ;
113  if _n_=1 then put "/* &schema.&outds (&nobs rows, &vars columns) */";
114  set &ds;
115  %if &maxobs ne max %then %do;
116  if _n_>&maxobs then stop;
117  %end;
118  length _____str $32767;
119  call missing(_____str);
120  format _numeric_ best.;
121  format _character_ ;
122  %local i comma var vtype vfmt;
123  %do i=1 %to %sysfunc(countw(&varlist));
124  %let var=%scan(&varlist,&i);
125  %let vtype=%mf_getvartype(&ds,&var);
126  %let vfmt=%upcase(%mf_getvarformat(&ds,&var,force=1));
127  %if &i=1 %then %do;
128  %if &flavour=SAS %then %do;
129  put "insert into &schema.&outds set ";
130  put " &var="@;
131  %end;
132  %else %if &flavour=PGSQL %then %do;
133  _____str=cats(
134  "INSERT INTO &schema.&outds ("
135  ,symget('varlistcomma')
136  ,") VALUES ("
137  );
138  put _____str;
139  put " "@;
140  %end;
141  %end;
142  %else %do;
143  %if &flavour=SAS %then %do;
144  put " ,&var="@;
145  %end;
146  %else %if &flavour=PGSQL %then %do;
147  put " ,"@;
148  %end;
149  %end;
150  %if &vtype=N %then %do;
151  %if &flavour=SAS %then %do;
152  put &var;
153  %end;
154  %else %if &flavour=PGSQL %then %do;
155  if missing(&var) then put 'NULL';
156  %if &applydttm=YES and "%substr(&vfmt.xxxxxxxx,1,8)"="DATETIME"
157  %then %do;
158  else put "TIMESTAMP '" &var E8601DT25.6 "'";
159  %end;
160  %else %do;
161  else put &var;
162  %end;
163  %end;
164  %end;
165  %else %do;
166  _____str="'"!!trim(tranwrd(&var,"'","''"))!!"'";
167  put _____str;
168  %end;
169  %end;
170  %if &flavour=SAS %then %do;
171  put ';';
172  %end;
173  %else %if &flavour=PGSQL %then %do;
174  put ');';
175  %end;
176 
177  if _n_=&nobs then put /;
178 run;
179 
180 %mend mp_ds2inserts;