Macros for SAS Application Developers
https://github.com/sasjs/core
Loading...
Searching...
No Matches
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;
78proc sql noprint;
79select 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 */
111data _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 /;
178run;
179
180%mend mp_ds2inserts;