Dear SAS community,
I have a large csv file with 100+ columns to be split into multiple smaller csv files by group key column in the csv files. On top of that, I would also like to further sum up the respective variables by group key. Simple illustration is as below
data
group | var1 | var2 | var3 | … | var100 |
group1 | abc | 1 | 1 | … | 1 |
group1 | def | 2 | 2 | … | 2 |
group1 | ghi | 3 | 3 | … | 3 |
group2 | jkl | 4 | 4 | … | 4 |
group2 | mno | 5 | 5 | … | 5 |
group2 | pqr | 6 | 6 | … | 6 |
group3 | stu | 7 | 7 | … | 7 |
group3 | vwx | 8 | 8 | … | 8 |
group3 | yza | 9 | 9 | … | 9 |
output
1. group1.csv
group | var2 | var3 | … | var100 |
group1 | 6 | 6 | … | 6 |
2. group2.csv
group | var2 | var3 | … | var100 |
group2 | 15 | 15 | … | 15 |
3. group3.csv
group | var2 | var3 | … | var100 |
group3 | 24 | 24 | … | 24 |
Is there any possible way to do this?
Thank you very much in advance.
%let csv= c:\temp\have.csv ; *the CSV file you have;
%let var=bp_status ; *the group variable for splitting;
%let out= c:\temp\ ; *the path where you want to keep splitted csv file;
libname out v9 "&out.";
proc import datafile="&csv." out=have dbms=csv replace;
run;
proc freq data=have noprint;
table &var./out=level;
run;
data _null_;
set level ;
call execute(catt("data temp;set have;if &var.='",&var.,"';run;
proc export data=temp outfile='&out.\",&var.,".csv' dbms=csv replace;run;"));
run;
First, summarize:
proc summary data=have;
by group;
var var:;
output out=want sum()=;
run;
then create the csv files:
data _null_;
set want;
length fvar $200;
fvar = "path to the files/" !! strip(group) !! ".csv";
file dummy filevar=fvar dlm="," dsd lrecl=32767;
put "group,var1,....var100"; /* expand this statement for all variables */
put group var1-var100;
run;
Do you really want a series of CSV files with 1 (2 with header) rows in each?
Why?
%let csv= c:\temp\have.csv ; *the CSV file you have;
%let var=bp_status ; *the group variable for splitting;
%let out= c:\temp\ ; *the path where you want to keep splitted csv file;
libname out v9 "&out.";
proc import datafile="&csv." out=have dbms=csv replace;
run;
proc freq data=have noprint;
table &var./out=level;
run;
data _null_;
set level ;
call execute(catt("data temp;set have;if &var.='",&var.,"';run;
proc export data=temp outfile='&out.\",&var.,".csv' dbms=csv replace;run;"));
run;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.