BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mattyj1234
Calcite | Level 5

Hello group.  I am new to SAS and am trying to figure out the best way to split a table into multiple files.  I got the code to select distinct values in a row as well as the count of distinct items in a row.  Ultimately I would like to export all columns related to a unique value in the "make" column into a file with the same name as the "make".  I would like to perform this for each unique "make" in the data set.  For example query SASHELP.CARS and export out all Acura info to the Acura file, Honda info to the Honda file, Chevy info to the Chevy file, etc....

/*counts unique*/
PROC SQL;
SELECT count(distinct(Make)) into :makecount FROM SASHELP.CARS;
quit;
%put &makecount;


/*creates single column table using only unique names*/
proc sql;
Create table Work.query as
select distinct(Make) from SASHELP.CARS;
run;
quit;
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

You want to write multiple text files from one SAS dataset and base the name of the file on the value of a variable in the dataset?

If so use the FILEVAR option on the FILE statement.

%let path=/my/target/directory/;
data _null_;
  set sashelp.cars ;
  link set_filename;
  put (_all_) (+0);
return;
set_filename:
  length fname $255 ;
  fname=cats(symget('path'),make,'.csv');
  file csv filevar=fname dsd  ;
run;

Example of one of the files generated:

2543  data _null_;
2544    infile "&path.Volvo.csv";
2545    input;
2546    put _infile_;
2547  run;

NOTE: The infile "c:\downloads\Volvo.csv" is:
      Filename=c:\downloads\Volvo.csv,
      RECFM=V,LRECL=32767,File Size (bytes)=1030,
      Last Modified=17Jan2020:09:48:21,
      Create Time=17Jan2020:09:48:21

Volvo,XC90 T6,SUV,Europe,All,"$41,250","$38,851",2.9,6,268,15,20,4638,113,189
Volvo,S40 4dr,Sedan,Europe,Front,"$25,135","$23,701",1.9,4,170,22,29,2767,101,178
Volvo,S60 2.5 4dr,Sedan,Europe,All,"$31,745","$29,916",2.5,5,208,20,27,3903,107,180
Volvo,S60 T5 4dr,Sedan,Europe,Front,"$34,845","$32,902",2.3,5,247,20,28,3766,107,180
Volvo,S60 R 4dr,Sedan,Europe,All,"$37,560","$35,382",2.5,5,300,18,25,3571,107,181
Volvo,S80 2.9 4dr,Sedan,Europe,Front,"$37,730","$35,542",2.9,6,208,20,28,3576,110,190
Volvo,S80 2.5T 4dr,Sedan,Europe,All,"$37,885","$35,688",2.5,5,194,20,27,3691,110,190
Volvo,C70 LPT convertible 2dr,Sedan,Europe,Front,"$40,565","$38,203",2.4,5,197,21,28,3450,105,186
Volvo,C70 HPT convertible 2dr,Sedan,Europe,Front,"$42,565","$40,083",2.3,5,242,20,26,3450,105,186
Volvo,S80 T6 4dr,Sedan,Europe,Front,"$45,210","$42,573",2.9,6,268,19,26,3653,110,190
Volvo,V40,Wagon,Europe,Front,"$26,135","$24,641",1.9,4,170,22,29,2822,101,180
Volvo,XC70,Wagon,Europe,All,"$35,145","$33,112",2.5,5,208,20,27,3823,109,186
NOTE: 12 records were read from the infile "c:\downloads\Volvo.csv".
      The minimum record length was 76.
      The maximum record length was 97.

 

View solution in original post

2 REPLIES 2
Tom
Super User Tom
Super User

You want to write multiple text files from one SAS dataset and base the name of the file on the value of a variable in the dataset?

If so use the FILEVAR option on the FILE statement.

%let path=/my/target/directory/;
data _null_;
  set sashelp.cars ;
  link set_filename;
  put (_all_) (+0);
return;
set_filename:
  length fname $255 ;
  fname=cats(symget('path'),make,'.csv');
  file csv filevar=fname dsd  ;
run;

Example of one of the files generated:

2543  data _null_;
2544    infile "&path.Volvo.csv";
2545    input;
2546    put _infile_;
2547  run;

NOTE: The infile "c:\downloads\Volvo.csv" is:
      Filename=c:\downloads\Volvo.csv,
      RECFM=V,LRECL=32767,File Size (bytes)=1030,
      Last Modified=17Jan2020:09:48:21,
      Create Time=17Jan2020:09:48:21

Volvo,XC90 T6,SUV,Europe,All,"$41,250","$38,851",2.9,6,268,15,20,4638,113,189
Volvo,S40 4dr,Sedan,Europe,Front,"$25,135","$23,701",1.9,4,170,22,29,2767,101,178
Volvo,S60 2.5 4dr,Sedan,Europe,All,"$31,745","$29,916",2.5,5,208,20,27,3903,107,180
Volvo,S60 T5 4dr,Sedan,Europe,Front,"$34,845","$32,902",2.3,5,247,20,28,3766,107,180
Volvo,S60 R 4dr,Sedan,Europe,All,"$37,560","$35,382",2.5,5,300,18,25,3571,107,181
Volvo,S80 2.9 4dr,Sedan,Europe,Front,"$37,730","$35,542",2.9,6,208,20,28,3576,110,190
Volvo,S80 2.5T 4dr,Sedan,Europe,All,"$37,885","$35,688",2.5,5,194,20,27,3691,110,190
Volvo,C70 LPT convertible 2dr,Sedan,Europe,Front,"$40,565","$38,203",2.4,5,197,21,28,3450,105,186
Volvo,C70 HPT convertible 2dr,Sedan,Europe,Front,"$42,565","$40,083",2.3,5,242,20,26,3450,105,186
Volvo,S80 T6 4dr,Sedan,Europe,Front,"$45,210","$42,573",2.9,6,268,19,26,3653,110,190
Volvo,V40,Wagon,Europe,Front,"$26,135","$24,641",1.9,4,170,22,29,2822,101,180
Volvo,XC70,Wagon,Europe,All,"$35,145","$33,112",2.5,5,208,20,27,3823,109,186
NOTE: 12 records were read from the infile "c:\downloads\Volvo.csv".
      The minimum record length was 76.
      The maximum record length was 97.

 

yabwon
Onyx | Level 15

Hi @mattyj1234 ,

 

I would start with something simple like:

 

%macro split(ds,var);
%local makecnt;
PROC SQL;
  SELECT distinct(&var.) 
  into :m1- 
  FROM &ds.;
  %let makecnt = &sqlobs.; 
QUIT;
%put &=makecnt. &=m1.;

data
  %do i = 1 %to &makecnt.;
    work.%sysfunc(compress(&&m&i.,_,kda))
  %end;
;
  set &ds.;

  select;
    %do i = 1 %to &makecnt.;
      when (&var. = "&&m&i.") output work.%sysfunc(compress(&&m&i.,_,kda));
    %end;
    otherwise put "Error: unknown value";
  end;
run;

%mend split;


%split(SASHELP.CARS,Make)

 

As long as there is not to much categories AND values are proper for dataset name (check out "Mercedes-Benz") is should give what you need.

 

All the best

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 763 views
  • 1 like
  • 3 in conversation