getrawdata.sas
Go to the documentation of this file.
1 /**
2  @file
3  @brief Downloads data in a variety of formats
4  @details To enable direct download, this service runs in a dedicated stream
5  as a GET request using URL parameters as inputs.
6 
7  The inputs are:
8 
9  @li table - the libds of the table to be downloaded
10  @li type - either SAS, CSV, EXCEL, MARKDOWN, WEBCSV or WEBTAB
11  @li filter - the filter RK if used
12 
13  <h4> SAS Macros </h4>
14  @li mf_verifymacvars.sas
15  @li mf_getuser.sas
16  @li mf_existfeature.sas
17  @li dc_assignlib.sas
18  @li mp_ds2cards.sas
19  @li mp_abort.sas
20  @li mp_binarycopy.sas
21  @li mp_cntlout.sas
22  @li mp_streamfile.sas
23  @li mpe_filtermaster.sas
24 
25 
26  @version 9.2
27  @author 4GL Apps Ltd
28  @copyright 4GL Apps Ltd. This code may only be used within Data Controller
29  and may not be re-distributed or re-sold without the express permission of
30  4GL Apps Ltd.
31 
32 **/
33 %global table type filter ds format is_fmt txfrom txto;
34 %mpeinit()
35 
36 %let user=%mf_getuser();
37 %let is_fmt=0;
38 
39 %mp_abort(iftrue= (%mf_verifymacvars(type table)=0)
40  ,mac=&_program..sas
41  ,msg=%str(Invalid inputs: type table)
42 )
43 
44 %let libds=%upcase(&table); /* actual source */
45 %let table=%upcase(&table); /* used as label for fmt catalogs */
46 %let lib=%scan(&table,1,.);
47 %let ds=%scan(&table,2,.);
48 %dc_assignlib(READ,&lib)
49 
50 data _null_;
51  set &mpelib..MPE_TABLES;
52  where upcase(libref)="&lib" and upcase(dsn)="&ds";
53 
54  /* if a TXTEMPORAL table then filter as such */
55  call symputx('txfrom',var_txfrom);
56  call symputx('txto',var_txto);
57 
58  ds=symget('ds');
59  is_fmt=0;
60  if subpad(cats(reverse(ds)),1,3)=:'CF-' then do;
61  ds=scan(ds,1,'-');
62  table=cats("&lib..",ds);
63  putlog "Format Catalog Captured";
64  is_fmt=1;
65  call symputx('libds','work.fmtextract');
66  call symputx('table',table);
67  end;
68  call symputx('is_fmt',is_fmt);
69  putlog (_all_)(=);
70 run;
71 
72 %mp_cntlout(
73  iftrue=(&is_fmt=1)
74  ,libcat=&table
75  ,fmtlist=0
76  ,cntlout=work.fmtextract
77 )
78 
79 %put preparing query;
80 %mpe_filtermaster(DLOAD,&libds,
81  dclib=&mpelib,
82  filter_rk=&filter,
83  outref=filtref,
84  outds=work.query
85 )
86 %put printing generated filterquery:;
87 data _null_;
88  infile filtref;
89  input;
90  putlog _infile_;
91 run;
92 
93 options obs=200000;/* stop limit */
94 data staged(drop=&txfrom &txto);
95  set &libds;
96  where %inc filtref;;
97 run;
98 options obs=max;
99 
100 options validvarname=upcase;
101 
102 %macro mpestp_getrawdata();
103  %local outfile;
104  %if &type=SAS %then %do;
105  %let outfile=%sysfunc(pathname(work))/&table..sas;
106  %mp_ds2cards(base_ds=staged
107  , tgt_ds=&table
108  , cards_file= "&outfile"
109  , maxobs=100000)
110  %let ext=sas;
111  %let mimetype=text;
112  %end;
113  %else %if &type=CSV or (&type=EXCEL and %mf_existfeature(EXPORTXLS) ne 1)
114  /* cannot proc export excel if PC Files is not licensed */
115  %then %do;
116  %let outfile=%sysfunc(pathname(work))/&table..csv;
117  PROC EXPORT DATA= staged
118  OUTFILE= "&outfile"
119  DBMS=csv REPLACE;
120  RUN;
121  %let ext=csv;
122  %let mimetype=csv;
123  %end;
124  %else %if &type=EXCEL %then %do;
125  %let ext=xlsx;
126  %let outfile=%sysfunc(pathname(work))/&table..&ext;
127  PROC EXPORT DATA= staged
128  OUTFILE= "&outfile"
129  DBMS=xlsx ;
130  RUN;
131  %let mimetype=XLSX;
132  %end;
133  %else %if &type=MARKDOWN %then %do;
134  %let ext=md;
135  %let outfile=%sysfunc(pathname(work))/&table..&ext;
136  filename mdref "&outfile" lrecl=32767;
137 
138  %mp_ds2md(staged,outref=mdref,showlog=NO)
139 
140  %let mimetype=MARKDOWN;
141  %end;
142  %else %if &type=WEBCSV %then %do;
143  PROC EXPORT DATA= staged
144  OUTFILE= _webout
145  DBMS=csv REPLACE;
146  RUN;
147  /* don't set headers */
148  %return;
149  %end;
150  %else %if &type=WEBTAB %then %do;
151  PROC EXPORT DATA= staged
152  OUTFILE= _webout
153  DBMS=tab REPLACE;
154  RUN;
155  /* don't set headers */
156  %return;
157  %end;
158  %else %do;
159  %mp_abort(msg=type &type not supported,mac=mpestp_getrawdata.sas);
160  %end;
161 
162  %mp_abort(iftrue= (&syscc ne 0)
163  ,mac=&_program..sas
164  ,msg=%str(syscc=&syscc)
165  )
166 
167  %mp_streamfile(contenttype=&mimetype
168  ,inloc=%str(&outfile)
169  ,outname=&table..&ext
170  )
171 
172 %mend mpestp_getrawdata;
173 %mpestp_getrawdata()
174 
175 %mpeterm()