Export a dataset to multiple CSV files

[Stu1979]

Hi all,

Need your help in a proc export procedure.

I have a dataset with several columns. I need to export that table into as many CSV files as distinct values in the first column  (let's call it "C1"), and name that CSV file something like "output_" and then the value of the C1. Basically almost a proc "export by".

Can anyone help me?

tks

Stu

[RichardinOz]

Proc export will not allow you to do what you want but you can achieve the result by selecting distinct values of C1 into a control table and then using the values to create suitable filenames to output to.  You can use proc export with a subset of your data (eg 9 rows) to create the code - throw away the output file but use recall to bring back the data _Null_ statements used to create a single file.

The solution will go something like this

Proc SQL Noprint ;

     Create table control as

     select distinct C1

     from have

Quit ;

%Macro WriteCSV (C1_value) ;

Filename &C1_value._out 'C:\temp\&C1_value._output.csv ' ;

Data _null_ ;

     Set have (where = (C1 = "&C1_Value")) ;

     file &C1_value._out ;

     < insert code from proc expost recall >

run ;

%Mend ;

data _Null_ ;

     length exec_code $ 64 ;

     set control ;

     exec_code = '%WriteCSV (#) ;' ;

     exec_code = TRANSTRN(exec_code,'#',C1) ;

     call execute (exec_code) ;

Run ;

(untested code)

Richard

[Stu1979]

Hey Richard,

tks for the quick response. I'm not a truly specialist using sas macros, so I apologize in advanced. I used your code and replaced with the proc export. The code exports indeed a CSV file but only one, and the name is "test_&C1_value._out.csv". It doesn't seem that it's sending the parameter to filter the table and "export by". Can you pls help me? Below is the code that I'm using.

Proc SQL Noprint ;

     Create table control as

     select distinct C1

     from have;

Quit ;

%Macro WriteCSV (C1_value) ;

Filename &C1_value._out 'C:\&C1_value._output.csv ' ;

Data _null_ ;

     Set have (where = (C1 = "&C1_Value")) ;

     file &C1_value._out ;

run;

proc export data=have replace

   outfile='c:\test_&C1_value._out.csv'

   dbms=csv;

run ;

%Mend ;

data _Null_ ;

     length exec_code $ 64 ;

     set control ;

     exec_code = '%WriteCSV (#) ;' ;

     exec_code = TRANSTRN(exec_code,'#',C1) ;

     call execute (exec_code) ;

Run ;

[RichardinOz]

Stu

I can't validate your code (no SAS handy) but I note one source of your problem which I confess I perpetrated:

Macro variable swill not resolve in single quotes.

Replace with double quotes

Filename &C1_value._out "C:\&C1_value._output.csv" ; (now redundant since you do not use the filename)

and

   outfile="c:\test_&C1_value._out.csv"

But do not replace in the expression

     exec_code = '%WriteCSV (#) ;' ;

because the intention is not to resolve during the datastep.

I was aware of the filevar option and by processing but thought my approach would be simplest to implement. I think you need a macro at some point unless you know in advance all the values C1 can take and that number does not change.

Richard

[BrunoMueller]

Hi have a look at the excellent blog post Implement BY processing for your entire SAS program - The SAS Dummy by it will help you to do what you want

[Reeza · Accepted solution]

Look up the filevar= option instead in a data step to dynamically change the output file.

No macros, no mess, no fuss.

SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition

Example of usage

[Stu1979]

Tks to all answers!

Actually the easiest solution was using the filevar option. Clean & easy code. Only issue is that it does not replace existing files with same name (it appends). To overcome that problem I just created a .bat file to delete all files from that folder a few minutes before the sas script runs. Workaround but efficient so far...

Stu.

[Tom]

It should replace the files, unless you add the MOD option to the FILE statement.

It will not remove existing files that are not present in the current data.  So if you are generating the filename based on variable NAME in the dataset and previously you ran it when there were records with NAME='fred' and this time you run it with data where there is no values of NAME='fred' then the file generated for fred before will NOT be erased.