topic need help exporting each line of the dataset into a dataset in SAS Programming

need help exporting each line of the dataset into a dataset

rykwong — Wed, 02 Jun 2021 19:31:37 GMT

Dear SAS community

I have a csv dataset (a flat sheet) that contains 100 fields of data from 75 patients. Each patient only has 1 line of data and each patient is identified by the variable "pt_id". I need to write a code to export each line from each patient into a separate csv file, thus generating 75 csv files.

do you have an example of such a code?

thanks

Re: need help exporting each line of the dataset into a dataset

Reeza — Wed, 02 Jun 2021 19:34:31 GMT

Do you want the filenames to be anything specific?
This is easily possible within a data step.

Re: need help exporting each line of the dataset into a dataset

Reeza — Wed, 02 Jun 2021 20:23:02 GMT

Here's an example of splitting by group, in your case PT_ID would be your group.
https://gist.github.com/statgeek/047bc83a85672f4dd546

~~The only issue with this code is it explicitly lists the variable names and you probably want to do that in a more automated fashion.....~~

EDIT: I updated my example in the link to be more generic, the file names will be the PT_ID as well in this example. HTH.

Re: need help exporting each line of the dataset into a dataset

rykwong — Thu, 03 Jun 2021 01:38:18 GMT

this works! thanks so much !!

Re: need help exporting each line of the dataset into a dataset

Sajid01 — Thu, 03 Jun 2021 12:30:01 GMT

Hello @rykwong
Nice that @Reeza solution solved your issue. Please that as a solution.

Re: need help exporting each line of the dataset into a dataset

rykwong — Fri, 04 Jun 2021 19:33:41 GMT

Hi Reeza

Much appreciated. what if the var_split is really from combining 2 variables. To create the unique pt_id I combined “subject_id” with “visit” by pt_id=subject_id||visit.

The code generated the single line dataset just fine, but it added the pt_id to the end of the dataset. I need to get rid of this variable “pt_id” from the exported datasheets. Alternatively can the code you provided me, handled 2 variables as the var_split?

Thanks

Re: need help exporting each line of the dataset into a dataset

Reeza — Fri, 04 Jun 2021 20:23:25 GMT

*name of the data set with the original data;
%let lib_name = sashelp;
%let dsn_name = class;
*Variable to split on;
%let var_split1 = NAME;
%let var_split2 = SEX;
*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_split1 &var_split2;
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.; *Variable that file is to be split on;

*Create path to file that is to be exported;
if first.&var_split2. 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_split2. then 
put "&var_list_csv.";

*Output variables;
put &var_list.;

run;

Untested, but this should work for two variables.

Re: need help exporting each line of the dataset into a dataset

rykwong — Thu, 10 Jun 2021 17:20:26 GMT

Dear Reeza

I ran it but encounter this error (please see attached log)

Many thanks for your help. Please let me know your thoughts

Thanks again

Re: need help exporting each line of the dataset into a dataset

Reeza — Thu, 10 Jun 2021 19:01:55 GMT

*name of the data set with the original data;
%let lib_name = sashelp;
%let dsn_name = class;
*Variable to split on;
%let var_split1 = NAME;
%let var_split2 = SEX;
*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_split1 &var_split2;
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.; *Variable that file is to be split on;

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

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

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

*Output variables;
put &var_list.;

run;

I've changed the line to as follows - this is the part that controls the file name.

if first.&var_split2. then out_file=cats("&path_folder.", trim(&var_split1.), "_", trim(&var_split2.), ".csv");

So now it will include the firstSplitVariable_secondSplitVariable in the name. If you'd like only one portion, remove it from the CATS() function.

Re: need help exporting each line of the dataset into a dataset

Tom — Thu, 10 Jun 2021 19:16:58 GMT

Note that the IF FIRST. is not needed when generating the filename. The FILE statement will take care of noticing when the value of the FILEVAR= variable changes and switch files automatically.

The SORT is not needed either especially if you are only writing one line per file. But if you are writing multiple lines to the same file then processing the records in filename order it will saving some time as it will prevent SAS from opening and closing the same file multiple times.

Re: need help exporting each line of the dataset into a dataset

Reeza — Fri, 11 Jun 2021 00:07:39 GMT

I think I had the same comment about the sort in the code 🙂

Thanks for the tip for the FILEVAR automatically detecting changes, that's a good thing to know!

This is a generic program, so it's intended to handle both a single record or multiple records, so a bit of overkill in places but generic enough to be useful IMO.