topic Re: Macro to export sas datset to CSV , with all columns (both numeric and char quoted in double quo in SAS Programming

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

learn_SAS_23 — Mon, 27 Jan 2025 15:21:27 GMT

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.

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

Tom — Mon, 27 Jan 2025 16:00:45 GMT

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 &in;
  put (_all_) (~);
run;
%mend qcsv;

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

PaigeMiller — Mon, 27 Jan 2025 16:03:44 GMT

Why does it have to be a macro?

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

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

learn_SAS_23 — Mon, 27 Jan 2025 20:00:44 GMT

Works exactly to my needs , Thanks for your help

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

learn_SAS_23 — Mon, 27 Jan 2025 20:01:40 GMT

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

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

Tom — Mon, 27 Jan 2025 23:29:26 GMT

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.

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

Ksharp — Tue, 28 Jan 2025 02:34:44 GMT

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;