Note I find it is generally much easier (and faster) to use the output of PROC CONTENTS than use SASHELP.VCOLUMNS (it is especially much faster if have a lot of librefs defined).
It will also make it easier to use FMTINFO() to find if there are any DATE variables.
Let's make a macro called DSN2CSVQ (Q for the goofy quoting required).
%macro dsn2csvq(dsn,fname);
* Get content information and order by varnum ;
proc contents data=&dsn noprint
out=contents(keep=libname memname varnum name type format label)
;
run;
proc sort data=contents; by varnum; run;
* Write header line with qutoes ;
data _null_;
file &fname dsd ;
set contents;
put name ~ @ ;
run;
* Generate code to write data lines ;
filename code temp;
data _null_;
file code;
set contents end=eof;
length nliteral $65 null $10;
if eof then null='''""''';
else null='''"",''';
nliteral=nliteral(name);
if type=2 or fmtinfo(format,'cat') in ('date','time','datetime') then do;
put 'if missing(' nliteral ') then put ' null '@; else put ' nliteral '~ @;';
end;
else put 'put ' nliteral '@;' ;
if eof then put 'put;';
run;
* Append data lines ;
data _null_;
file &fname dsd mod ;
set &dsn;
%include code / source2;
run;
%mend dsn2csvq;
Now let's make a sample dataset and try using it.
data have;
set sashelp.class(obs=5);
date=today();
format date yymmdd10.;
if _n_=1 then call missing(name);
if _n_=2 then call missing(sex);
if _n_=3 then call missing(age);
if _n_=4 then call missing(date);
run;
filename csv temp;
%dsn2csvq(dsn=have,fname=csv);
You can see in the log that it generate and ran this code to write the data lines:
53 +if missing(Name ) then put '"",' @; else put Name ~ @;
54 +if missing(Sex ) then put '"",' @; else put Sex ~ @;
55 +put Age @;
56 +put Height @;
57 +put Weight @;
58 +if missing(date ) then put '""' @; else put date ~ @;
59 +put;
And here the resulting CSV file.
"Name","Sex","Age","Height","Weight","date"
"","M",14,69,112.5,"2024-05-18"
"Alice","",13,56.5,84,"2024-05-18"
"Barbara","F",,65.3,98,"2024-05-18"
"Carol","F",14,62.8,102.5,""
"Henry","M",14,63.5,102.5,"2024-05-18"
... View more