topic Export Distinct rows to distinct csv in SAS Programming

Export Distinct rows to distinct csv

mattyj1234 — Fri, 17 Jan 2020 14:29:41 GMT

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;

Re: Export Distinct rows to distinct csv

Tom — Fri, 17 Jan 2020 15:08:37 GMT

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.

Re: Export Distinct rows to distinct csv

yabwon — Fri, 17 Jan 2020 14:55:47 GMT

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