Trying to proc export individual CSV files with filenames based on a list of filter values

[Callmesnacks]

Hi SAS folks, 

Recently, I moved from using the SAS EG GUI to implementing bits of my own code to expedite and streamline many of the projects that I work on regularly.

After querying a database to obtain a very large dataset, I'm trying to export filtered versions of the resulting dataset into individual CSV files for use by the rest of my team. If I'm able to make this work, I could theoretically schedule and automate a good number of these processes (but that's a question for later 😁).

That being said, I'm running into two issues during the export process (of note, I can hardcode in these two values, and the code works, but that defeats the purpose of the automation):

1. The where clause in the data = line needs to be populated with each of the possible values contained in the dataset (it's a list of states)
2. Each file needs a unique filename (preferably related to the filter value, but this is less important)

I've gotten here so far, but now I'm at a loss. 

%let path = "//data/dev/XYZ/"
%let type = ".CSV"
Proc Export Data=WORK.QUERY_FOR_QDM_TGT_MEMBER_ROSTER2 (where=(state = 'OH'))
outfile=&Path..&Type
dbms = CSV replace;
run;

I attempted to use the macro below after browsing the forum (using &nextstate in both the filter and filename), but I didn't know where to put everything in relation to the proc export step above, nor do I have a strong understanding of many of the function calls therein to modify it myself.

%let state = "NY", "OH";  %put &State;

%macro Getstates;
   %do i=1 %to %sysfunc(countw(%bquote(&state),%str(,)));
      %let nextstate= %scan(%bquote(&State),&i);
      %put Nextstate is &nextstate ;
   %end;
%mend;

%Getstates

Can anyone help, it'd be greatly appreciated! 

Thanks in advance, 

-Max

[Reeza]

If I may not so humbly recommend this tutorial:
Tutorial on converting a working program to a macro

This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

If you're doing this by State you'll be running into some interesting Macro issues such as when dealing with the state of OR which is OR. 

TBH though, it's actually quite easy to pipe a CSV file to mulitple CSV files straight from a basic data step - no macros needed at all.

See this generalized code I wrote a few weeks ago for someone else. You could easily wrap this into a macro as well.

*name of the data set with the original data;
%let lib_name = sashelp;
%let dsn_name = class;
*Variable to split on;
%let var_split = NAME;
*path to folder to save text files;
%let path_folder= /home/fkhurshed/;




*if you are exporting each line this is not required 
but should not  cause any issues unless your data set is large. In that case rename your data set to _temp and skip this step;
PROC SORT DATA=&lib_name..&dsn_name OUT=_temp;
BY &var_split;
RUN;



*make variable lists;
*for header row;
proc sql noprint;
select name into :var_list_csv separated by ", " from sashelp.vcolumn where libname = upper("&lib_name") and memname = upper("&dsn_name");
select name into :var_list separated by " " from sashelp.vcolumn where libname = upper("&lib_name") and memname = upper("&dsn_name");
quit;



DATA _NULL_;

SET _temp; *Dataset to be exported;
BY &var_split.; *Variable that file is to be split on;

*Create path to file that is to be exported;
if first.&var_split. then out_file=cats("&path_folder.", &var_split., ".csv");

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

*If first value of make then output column names;
if first.&var_split. then 
put "&var_list_csv.";

*Output variables;
put &var_list.;

run;

---

@Callmesnacks wrote:

Hi SAS folks, 

Recently, I moved from using the SAS EG GUI to implementing bits of my own code to expedite and streamline many of the projects that I work on regularly.

After querying a database to obtain a very large dataset, I'm trying to export filtered versions of the resulting dataset into individual CSV files for use by the rest of my team. If I'm able to make this work, I could theoretically schedule and automate a good number of these processes (but that's a question for later 😁).

That being said, I'm running into two issues during the export process (of note, I can hardcode in these two values, and the code works, but that defeats the purpose of the automation):

1. The where clause in the data = line needs to be populated with each of the possible values contained in the dataset (it's a list of states)
2. Each file needs a unique filename (preferably related to the filter value, but this is less important)

I've gotten here so far, but now I'm at a loss. 

%let path = "//data/dev/XYZ/"
%let type = ".CSV"
Proc Export Data=WORK.QUERY_FOR_QDM_TGT_MEMBER_ROSTER2 (where=(state = 'OH'))
outfile=&Path..&Type
dbms = CSV replace;
run;

I attempted to use the macro below after browsing the forum (using &nextstate in both the filter and filename), but I didn't know where to put everything in relation to the proc export step above, nor do I have a strong understanding of many of the function calls therein to modify it myself.

%let state = "NY", "OH";  %put &State;

%macro Getstates;
   %do i=1 %to %sysfunc(countw(%bquote(&state),%str(,)));
      %let nextstate= %scan(%bquote(&State),&i);
      %put Nextjob is &nextjob ;
   %end;
%mend;

%Getstates

Can anyone help, it'd be greatly appreciated! 

Thanks in advance, 

-Max

---

[Kurt_Bremser]

You can write several files, depending group values, in one data step, and you do not even need to sort:

data _null_;
set sashelp.class;
fname = '/folders/myfolders/class_' !! strip(sex) !! '.csv';
file dummy dlm="," filevar=fname mod;
put name sex;
run;

If you need to add a header line, sort first, and then use BY variable in the data step and write the header line at FIRST.variable.

[Callmesnacks]

Thanks for the quick response!

I didn't even consider not using proc export. Although, now I'm going to need quite a bit of handholding.

In the example above, I've got the entire table here: WORK.QUERY_FOR_QDM_TGT_MEMBER_ROSTER2 which, if exported wholesale as a CSV is formatted exactly as I need it (albeit, being enormous), this includes a header row.

Ultimately, I need a file for each state (the field is actually called state) with all of the values from the results of the query above as the people using these files will most likely be manipulating them in Excel

As such, where would I put the WORK.QUERY_FOR_QDM_TGT_MEMBER_ROSTER2 in the code snippet above (I imagine that I swap out sex for state and my filepath for the fname field) and is there anything else I'd need to change?

[Reeza · Accepted solution]

My solution is that idea, generalized. You really only need to provide the table name, it will go use the data dictionaries to get the column names and header row. 

If you update the %let PATH_FOLDER and run the following code what do you get?

*name of the data set with the original data;
%let lib_name = work;
%let dsn_name = QUERY_FOR_QDM_TGT_MEMBER_ROSTER2;
*Variable to split on;
%let var_split = STATE;
*path to folder to save text files;
%let path_folder= /home/fkhurshed/;




*if you are exporting each line this is not required 
but should not  cause any issues unless your data set is large. In that case rename your data set to _temp and skip this step;
PROC SORT DATA=&lib_name..&dsn_name OUT=_temp;
BY &var_split;
RUN;



*make variable lists;
*for header row;
proc sql noprint;
select name into :var_list_csv separated by ", " from sashelp.vcolumn where libname = upper("&lib_name") and memname = upper("&dsn_name");
select name into :var_list separated by " " from sashelp.vcolumn where libname = upper("&lib_name") and memname = upper("&dsn_name");
quit;



DATA _NULL_;

SET _temp; *Dataset to be exported;
BY &var_split.; *Variable that file is to be split on;

*Create path to file that is to be exported;
if first.&var_split. then out_file=cats("&path_folder.", &var_split., ".csv");

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

*If first value of make then output column names;
if first.&var_split. then 
put "&var_list_csv.";

*Output variables;
put &var_list.;

run;  

[Callmesnacks]

Perfect!
That did exactly what I needed.
Now I just need to break down each step to understand what's going on :-).
Thanks!

[Tom]

No need to use PROC EXPORT to write a delimited file.  Just use a data step.  And if you are using a data step then you can use the FILEVAR= option on the FILE statement to direct different observations to different files.

Here is an example using SASHELP.CLASS and SEX.

proc sort data=sashelp.class out=class;
  by sex;
run;

%let path=%sysfunc(pathname(work))\;
%let dsn=class;
%let byvar=sex;


data _null_;
  set &dsn;
  length __fname_ $256 ;
  by &byvar;
  __fname_=cats("&path.class_",sex,'.csv');
  file csv dsd lrecl=2000000 filevar=__fname_;
  if first.sex then link names;
  put (_all_)(+0);
return;
names:
  length __name_ $255;
  do while(1);
    call vnext(__name_);
    if lowcase(__name_) = ('__fname_') then leave;
    __name_ = vlabelx(__name_);
    put __name_ @;
  end;
  put; 
run; 

If you want the names instead of the labels as the column headers remove the line that is calling VLABELX().

Results:

1168  filename files "&path";
1169  options generic;
1170  data _null_;
1171    length fname $256;
1172    infile files('*.csv') filename=fname;
1173    input ;
1174    fname=scan(fname,-1,'/\');
1175    if fname ne lag(fname) then put / fname= / 25*'-' ;
1176    put _infile_;
1177  run;

NOTE: The infile library FILES is:
      (system-specific pathname),
      (system-specific file attributes)

NOTE: The infile FILES('*.csv') is:
      (system-specific pathname),
      (system-specific file attributes)


fname=class_F.csv
-------------------------
Name,Sex,Age,Height,Weight
Alice,F,13,56.5,84
Barbara,F,13,65.3,98
Carol,F,14,62.8,102.5
Jane,F,12,59.8,84.5
Janet,F,15,62.5,112.5
Joyce,F,11,51.3,50.5
Judy,F,14,64.3,90
Louise,F,12,56.3,77
Mary,F,15,66.5,112
NOTE: The infile FILES('*.csv') is:
      (system-specific pathname),
      (system-specific file attributes)


fname=class_M.csv
-------------------------
Name,Sex,Age,Height,Weight
Alfred,M,14,69,112.5
Henry,M,14,63.5,102.5
James,M,12,57.3,83
Jeffrey,M,13,62.5,84
John,M,12,59,99.5
Philip,M,16,72,150
Robert,M,12,64.8,128
Ronald,M,15,67,133
Thomas,M,11,57.5,85
William,M,15,66.5,112
NOTE: 21 records were read from the infile (system-specific pathname).
      The minimum record length was 17.
      The maximum record length was 26.
NOTE: 10 records were read from the infile (system-specific pathname).
      The minimum record length was 17.
      The maximum record length was 26.
NOTE: 11 records were read from the infile (system-specific pathname).
      The minimum record length was 17.
      The maximum record length was 26.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


1178  options nogeneric;

[andreas_lds]

SAS has a macro for exporting csv-files, called ds2csv:

proc sql noprint;
   create view work.files as
      select distinct Sex
         from sashelp.class
   ;
quit;

/* no quotes! */
%let path = //data/dev/XYZ/;


data _null_;
   set work.files;
   
   length command $ 250;
   /* macro vars won't be resolved in command, but later during call execute */
   command = cats('%nrstr(%ds2csv(data=sashelp.class, runmode=b, csvfile=&path/class_', Sex, '.csv, where=Sex="',Sex, '"));');
   put command=;
   
   call execute(command);
run;