Production Ready Macros for SAS Application Developers
https://github.com/sasjs/core
mp_lib2inserts.sas
Go to the documentation of this file.
1/**
2 @file
3 @brief Convert all data in a library to SQL insert statements
4 @details Gets list of members then calls the <code>%mp_ds2inserts()</code>
5 macro.
6 Usage:
7
8 %mp_getddl(sashelp, schema=work, fref=tempref)
9
10 %mp_lib2inserts(sashelp, schema=work, outref=tempref)
11
12 %inc tempref;
13
14
15 The output will be one file in the outref fileref.
16
17
18 <h4> SAS Macros </h4>
19 @li mp_ds2inserts.sas
20
21
22 @param [in] lib Library in which to convert all datasets to inserts
23 @param [in] flavour= (SAS) The SQL flavour to be applied to the output. Valid
24 options:
25 @li SAS (default) - suitable for regular proc sql
26 @li PGSQL - Used for Postgres databases
27 @param [in] maxobs= (max) The max number of observations (per table) to create
28 @param [out] outref= Output fileref in which to create the insert statements.
29 If it exists, it will be appended to, otherwise it will be created.
30 @param [out] schema= (0) The schema of the target database, or the libref.
31 @param [in] applydttm= (YES) If YES, any columns using datetime formats will
32 be converted to native DB datetime literals
33
34 @version 9.2
35 @author Allan Bowe
36**/
37
38%macro mp_lib2inserts(lib
39 ,flavour=SAS
40 ,outref=0
41 ,schema=0
42 ,maxobs=max
43 ,applydttm=YES
44)/*/STORE SOURCE*/;
45
46/* Find the tables */
47%local x ds memlist;
48proc sql noprint;
49select distinct lowcase(memname)
50 into: memlist
51 separated by ' '
52 from dictionary.tables
53 where upcase(libname)="%upcase(&lib)"
54 and memtype='DATA'; /* exclude views */
55
56
57%let flavour=%upcase(&flavour);
58%if &flavour ne SAS and &flavour ne PGSQL %then %do;
59 %put %str(WAR)NING: &flavour is not supported;
60 %return;
61%end;
62
63
64/* create the inserts */
65%do x=1 %to %sysfunc(countw(&memlist));
66 %let ds=%scan(&memlist,&x);
67 %mp_ds2inserts(&lib..&ds
68 ,outref=&outref
69 ,schema=&schema
70 ,outds=&ds
71 ,flavour=&flavour
72 ,maxobs=&maxobs
73 ,applydttm=&applydttm
74 )
75%end;
76
77%mend mp_lib2inserts;