I'd like to export a large SAS dataset to multiple SAS files using 3 BY groups in the filename. It needs to be flexible since the BY group variables can change from set to set.
In experimenting, I found this outstanding thread (https://communities.sas.com/t5/SAS-Programming/need-help-exporting-each-line-of-the-dataset-into-a-dataset/m-p/745297#M233591) that seems to do what I want, at least in in terms of creating filenames using the by group variable names. However, that code saves the files as csv files. I want to create SAS files (with filenames using the by group variables). So, while I started to adapt it for my files; ultimately I may need to start from scratch to reach my goal.
*name of the data set with the original data;
%let lib_name = work;
%let dsn_name = dsn1;
*Variable to split on;
%let var_split1 = YEAR;
%let var_split2 = MONTH;
%let var_split3 = Direction;
*path to folder to save text files;
%let path_folder= E:\Import Statistics\Warehouse\;
*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_split1 &var_split2 &var_split3;
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_split1 &var_split2. &var_split3.; *Variable that file is to be split on;
*Create path to file that is to be exported;
if first.&var_split3. then out_file=cats("&path_folder.", trim(&var_split1.), "_", trim(&var_split2.), "_", trim(&var_split3.), ".csv");
file temp filevar=out_file dlm=',' dsd;
*If first value of make then output column names;
if first.&var_split3. then
put "&var_list_csv.";
*Output variables;
put &var_list.;
run;
... View more
