topic Re: Export a dataset to multiple CSV files in SAS Programming

Export a dataset to multiple CSV files

Stu1979 — Thu, 06 Mar 2014 06:12:35 GMT

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

Re: Export a dataset to multiple CSV files

RichardinOz — Thu, 06 Mar 2014 07:36:33 GMT

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

Re: Export a dataset to multiple CSV files

BrunoMueller — Thu, 06 Mar 2014 09:46:08 GMT

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

Re: Export a dataset to multiple CSV files

Stu1979 — Fri, 07 Mar 2014 03:33:18 GMT

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 ;

Re: Export a dataset to multiple CSV files

Reeza — Fri, 07 Mar 2014 03:50:32 GMT

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

Re: Export a dataset to multiple CSV files

RichardinOz — Fri, 07 Mar 2014 04:38:23 GMT

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

Re: Export a dataset to multiple CSV files

Stu1979 — Sat, 08 Mar 2014 00:58:46 GMT

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.

Re: Export a dataset to multiple CSV files

Tom — Sat, 08 Mar 2014 01:26:33 GMT

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.