Macros for SAS Application Developers
https://github.com/sasjs/core
mp_ds2csv.sas
Go to the documentation of this file.
1 /**
2  @file
3  @brief Export a dataset to a CSV file WITH leading blanks
4  @details Export a dataset to a file or fileref, retaining leading blanks.
5 
6  When using SASJS headerformat, the input statement is provided in the first
7  row of the CSV.
8 
9  Usage:
10 
11  %mp_ds2csv(sashelp.class,outref="%sysfunc(pathname(work))/file.csv")
12 
13  filename example temp;
14  %mp_ds2csv(sashelp.air,outref=example,headerformat=SASJS)
15  data; infile example; input;put _infile_; if _n_>5 then stop;run;
16 
17  data _null_;
18  infile example;
19  input;
20  call symputx('stmnt',_infile_);
21  stop;
22  run;
23  data work.want;
24  infile example dsd firstobs=2;
25  input &stmnt;
26  run;
27 
28  Why use mp_ds2csv over, say, proc export?
29 
30  1. Ability to retain leading blanks (this is a major one)
31  2. Control the header format
32  3. Simple one-liner
33 
34  @param [in] ds The dataset to be exported
35  @param [in] dlm= (COMMA) The delimeter to apply. For SASJS, will always be
36  COMMA. Supported values:
37  @li COMMA
38  @li SEMICOLON
39  @param [in] headerformat= (LABEL) The format to use for the header section.
40  Valid values:
41  @li LABEL - Use the variable label (or name, if blank)
42  @li NAME - Use the variable name
43  @li SASJS - Used to create sasjs-formatted input CSVs, eg for use in
44  mp_testservice.sas. This format will supply an input statement in the
45  first row, making ingestion by datastep a breeze. Special misisng values
46  will be prefixed with a period (eg `.A`) to enable ingestion on both SAS 9
47  and Viya. Dates / Datetimes etc are identified by the format type (lookup
48  with mcf_getfmttype.sas) and converted to human readable formats (not
49  numbers).
50  @param [out] outfile= The output filename - should be quoted.
51  @param [out] outref= (0) The output fileref (takes precedence if provided)
52  @param [in] outencoding= (0) The (quoted) output encoding to use, eg `"UTF-8"`
53  @param [in] termstr= (CRLF) The line seperator to use. For SASJS, will
54  always be CRLF. Valid values:
55  @li CRLF
56  @li LF
57 
58  <h4> SAS Macros </h4>
59  @li mcf_getfmttype.sas
60  @li mf_getuniquename.sas
61  @li mf_getvarformat.sas
62  @li mf_getvarlist.sas
63  @li mf_getvartype.sas
64 
65  @version 9.2
66  @author Allan Bowe (credit mjsq)
67 **/
68 
69 %macro mp_ds2csv(ds
70  ,dlm=COMMA
71  ,outref=0
72  ,outfile=
73  ,outencoding=0
74  ,headerformat=LABEL
75  ,termstr=CRLF
76 )/*/STORE SOURCE*/;
77 
78 %local outloc delim i varlist var vcnt vat dsv vcom vmiss fmttype vfmt;
79 
80 %if not %sysfunc(exist(&ds)) %then %do;
81  %put %str(WARN)ING: &ds does not exist;
82  %return;
83 %end;
84 
85 %if %index(&ds,.)=0 %then %let ds=WORK.&ds;
86 
87 %if &outencoding=0 %then %let outencoding=;
88 %else %let outencoding=encoding=&outencoding;
89 
90 %if &outref=0 %then %let outloc=&outfile;
91 %else %let outloc=&outref;
92 
93 %if &headerformat=SASJS %then %do;
94  %let delim=",";
95  %let termstr=CRLF;
96  %mcf_getfmttype(wrap=YES)
97 %end;
98 %else %if &dlm=COMMA %then %let delim=",";
99 %else %let delim=";";
100 
101 /* credit to mjsq - https://stackoverflow.com/a/55642267 */
102 
103 /* first get headers */
104 data _null_;
105  file &outloc &outencoding lrecl=32767 termstr=&termstr;
106  length header $ 2000 varnm vfmt $32 dlm $1 fmttype $8;
107  call missing(of _all_);
108  dsid=open("&ds.","i");
109  num=attrn(dsid,"nvars");
110  dlm=&delim;
111  do i=1 to num;
112  varnm=upcase(varname(dsid,i));
113  if i=num then dlm='';
114  %if &headerformat=NAME %then %do;
115  header=cats(varnm,dlm);
116  %end;
117  %else %if &headerformat=LABEL %then %do;
118  header = cats(coalescec(varlabel(dsid,i),varnm),dlm);
119  %end;
120  %else %if &headerformat=SASJS %then %do;
121  if vartype(dsid,i)='C' then header=cats(varnm,':$char',varlen(dsid,i),'.');
122  else do;
123  vfmt=coalescec(varfmt(dsid,i),'0');
124  fmttype=mcf_getfmttype(vfmt);
125  if fmttype='DATE' then header=cats(varnm,':date9.');
126  else if fmttype='DATETIME' then header=cats(varnm,':E8601DT26.6');
127  else if fmttype='TIME' then header=cats(varnm,':TIME12.');
128  else header=cats(varnm,':best.');
129  end;
130  %end;
131  %else %do;
132  %put &sysmacroname: Invalid headerformat value (&headerformat);
133  %return;
134  %end;
135  put header @;
136  end;
137  rc=close(dsid);
138 run;
139 
140 %let varlist=%mf_getvarlist(&ds);
141 %let vcnt=%sysfunc(countw(&varlist));
142 
143 /**
144  * The $quote modifier (without a width) will take the length from the variable
145  * and increase by two. However this will lead to truncation where the value
146  * contains double quotes (which are doubled up). To get around this, scan the
147  * data to see the max number of double quotes, so that the appropriate width
148  * can be applied in the subsequent step.
149  */
150 data _null_;
151  set &ds end=last;
152 %do i=1 %to &vcnt;
153  %let var=%scan(&varlist,&i);
154  %if %mf_getvartype(&ds,&var)=C %then %do;
155  %let dsv1=%mf_getuniquename(prefix=csvcol1_);
156  %let dsv2=%mf_getuniquename(prefix=csvcol2_);
157  retain &dsv1 0;
158  &dsv2=length(&var)+countc(&var,'"');
159  if &dsv2>&dsv1 then &dsv1=&dsv2;
160  if last then call symputx(
161  "vlen&i"
162  /* should be no shorter than varlen, and no longer than 32767 */
163  ,cats('$quote',min(&dsv1+2,32767),'.')
164  ,'l'
165  );
166  %end;
167 %end;
168 
169 %let vat=@;
170 %let vcom=&delim;
171 %let vmiss=%mf_getuniquename(prefix=csvcol3_);
172 /* next, export data */
173 data _null_;
174  set &ds.;
175  file &outloc mod dlm=&delim dsd &outencoding lrecl=32767 termstr=&termstr;
176  if _n_=1 then &vmiss=' ';
177  %do i=1 %to &vcnt;
178  %let var=%scan(&varlist,&i);
179  %if &i=&vcnt %then %do;
180  %let vat=;
181  %let vcom=;
182  %end;
183  %if %mf_getvartype(&ds,&var)=N %then %do;
184  %if &headerformat = SASJS %then %do;
185  %let vcom=&delim;
186  %let fmttype=%sysfunc(mcf_getfmttype(%mf_getvarformat(&ds,&var)0));
187  %if &fmttype=DATE %then %let vfmt=DATE9.;
188  %else %if &fmttype=DATETIME %then %let vfmt=E8601DT26.6;
189  %else %if &fmttype=TIME %then %let vfmt=TIME12.;
190  %else %do;
191  %let vfmt=;
192  %let vcom=;
193  %end;
194  %end;
195  %else %let vcom=;
196 
197  /* must use period - in order to work in both 9.4 and Viya 3.5 */
198  if missing(&var) and &var ne %sysfunc(getoption(MISSING)) then do;
199  &vmiss=cats('.',&var);
200  put &vmiss &vat;
201  end;
202  else put &var &vfmt &vcom &vat;
203 
204  %end;
205  %else %do;
206  %if &i ne &vcnt %then %let vcom=&delim;
207  put &var &&vlen&i &vcom &vat;
208  %end;
209  %end;
210 run;
211 
212 %mend mp_ds2csv;