topic Re: Create separate CSVs for each record in a SAS dataset in SAS Programming

Create separate CSVs for each record in a SAS dataset

Suminder — Sat, 23 Nov 2019 21:38:38 GMT

Hi Friends,

I have following sas dataset

Name Age Country

Sam 15 CA

Rohan 13 USA

Mich 16 IND

the result required is separate CSVs for each record in above dataset. In this case it will be three CSVs with one record each.

Re: Create separate CSVs for each record in a SAS dataset

Kurt_Bremser — Sat, 23 Nov 2019 22:04:30 GMT

Use the filevar= option in the file statement.

Re: Create separate CSVs for each record in a SAS dataset

Reeza — Sat, 23 Nov 2019 22:17:32 GMT

Do you need to control the names?

If not ODS CSVALL is a good option.



ods csvall body = '/home/userName/Demo1/demo.csv' newfile=bygroup;

options nobyline;

proc print data=have noobs;
by name;
var name age country;
run;

ods csvall close;
options byline;

Re: Create separate CSVs for each record in a SAS dataset

Reeza — Sat, 23 Nov 2019 22:22:21 GMT

And another example if you want to use a FILEVAR approach.

https://gist.github.com/statgeek/047bc83a85672f4dd546

Change the folders/myfolders section and you can run the code and test it yourself and then adapt it for your data needs.

Two other things to think about:

Filename changing for each record, what are the rules
Does each file need a header record

PROC SORT DATA=SASHELP.CARS OUT=CARS;
BY make;
RUN;


DATA _NULL_;

SET cars; *Dataset to be exported;
BY make; *Variable that file is to be split on;

*Create path to file that is to be exported;
if first.make then out_file=cats('/folders/myfolders/', trim(make));

file temp filevar=out_file dlm=',' dsd;

*If first value of make then output column names;
if first.make then 
put 'Make, Model, MPG_HIGHWAY, MPG_CITY';

*Output variables;
put make model mpg_highway mpg_city;

run;

Re: Create separate CSVs for each record in a SAS dataset

Suminder — Sat, 23 Nov 2019 23:14:37 GMT

the rules are as follows:

the first csv will be named as output1.csv
the second csv will be named as output2.csv

output csvs does not require headers.

note:

there is a column in the source dataset that is rownumber...

Re: Create separate CSVs for each record in a SAS dataset

Tom — Sun, 24 Nov 2019 15:58:55 GMT

@Suminder wrote:

the rules are as follows:

1)

the first csv will be named as output1.csv
the second csv will be named as output2.csv

2)

output csvs does not require headers.

note:

there is a column in the source dataset that is rownumber...

Build the filename as a variable in the data step that is writing the files. You can then use the FILEVAR= option on the FILE statement to have SAS make many different files.

data _null_;
  set have ;
  length fname $200 ;
  fname = cats('output',rownumber,'.csv');
  file dummy filevar=fname dsd ;
  put Name Age Country ;
run;

Remember to add the directory to the FNAME value or else output1.csv etc will be created in the current working directory of process that is running SAS.