Macro to export sas datset to CSV , with all columns (both numeric and char quoted in double quotes

[learn_SAS_23]

Hello Team , 

Am looking for a generic macro , which exports data from table to CSV , with UTF8 encoding  and all data  (both numeric and character )

needs to be quoted in double quotes 

Can somebody help with sample code.

[Tom · Accepted solution]

Before you create a MACRO figure out what SAS code you want the macro to generate.

Perhaps something like this? First write the header line and then append the data lines.

So assuming you have defined a fileref named CSV that points to where you want to write the CSV file this code will make a copy of SASHELP.CLASS with quotes around everything.

proc transpose data=sashelp.class(obs=0) out=names; var _all_ ; run;
data _null_;
  file csv dsd lrecl=1000000;
  set names;
  put _name_ ~ @;
run;
data _null_;
  file csv dsd mod lrecl=1000000;
  set sashelp.class;
  put (_all_) (~);
run;

Result

 84         data _null_;
 85           infile csv;
 86           input;
 87           put _infile_;
 88         run;
 
 NOTE: The infile CSV is:
       Filename=/saswork/.../#LN00154,
       Owner Name=xxx,Group Name=oda,
       Access Permission=-rw-r--r--,
       Last Modified=27Jan2025:10:52:39,
       File Size (bytes)=611
 
 "Name","Sex","Age","Height","Weight"
 "Alfred","M","14","69","112.5"
 "Alice","F","13","56.5","84"
 "Barbara","F","13","65.3","98"
 "Carol","F","14","62.8","102.5"
 "Henry","M","14","63.5","102.5"
 "James","M","12","57.3","83"
 "Jane","F","12","59.8","84.5"
 "Janet","F","15","62.5","112.5"
 "Jeffrey","M","13","62.5","84"
 "John","M","12","59","99.5"
 "Joyce","F","11","51.3","50.5"
 "Judy","F","14","64.3","90"
 "Louise","F","12","56.3","77"
 "Mary","F","15","66.5","112"
 "Philip","M","16","72","150"
 "Robert","M","12","64.8","128"
 "Ronald","M","15","67","133"
 "Thomas","M","11","57.5","85"
 "William","M","15","66.5","112"
 NOTE: 20 records were read from the infile CSV.
       The minimum record length was 27.
       The maximum record length was 36.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds

To convert into a macro figure out what inputs the macro needs.  Perhaps IN for dataset name and OUT for file name?

%macro qcsv(in,out);
filename csv "&out" encoding='utf-8';
proc transpose data=&in(obs=0); var _all_; run;
data _null_;
  file csv dsd lrecl=1000000;
  set ;
  put _name_ ~ @ ;
run;
data _null_;
  file csv dsd lrecl=1000000;
  set ∈
  put (_all_) (~);
run;
%mend qcsv;

[learn_SAS_23]

Works exactly to my needs , Thanks for your help

[PaigeMiller]

Why does it have to be a macro?

You could just convert all numerics to character variables, and then create the CSV.

[learn_SAS_23]

I tried this option , it generates much code than above method

[Tom]

You could always just post-process the CSV file to add the quotes.

Say you have a standard CSV file name have.csv here is code to convert to your non-standard format into a file named want.csv.

data _null_;
  infile 'have.csv' dsd truncover length=ll column=cc lrecl=1000000;
  file 'want.csv' dsd lrecl=1000000;
  do until(ll>cc);
    input value :$200. @;
    put value ~ @;
  end;
  put;
run;

If you have some variables that require more than 200 bytes just increase the width on the informat.

[Ksharp]

I know Tom already gave you the solution.

Here is just an alternative solution, just for having some fun.

ods _all_ close;
ods noresults;
/* Quoted_columns=
A list of column numbers that indicate which values should be quoted
*/
ods csv file='c:\temp\temp.csv' options(Quoted_columns='1 2 3 4 5 6 ');
proc print data=sashelp.class noobs;run;
ods csv close;