topic Re: Exporting a dataset to csv based on variable using macro. For example, 'City' in the example in SAS Programming

Exporting a dataset to csv based on variable using macro. For example, 'City' in the example

ShashankB1987 — Wed, 12 May 2021 16:00:51 GMT

data WORK.All_City_Sales; infile datalines dsd truncover; input Date mmddyy10. City:$1. Units; informat Date mmddyy10.; format Data mmddyy10.; datalines; 01-01-2015 A 61 02-01-2015 A 29 03-01-2015 B 70 04-01-2015 B 35 05-01-2015 C 70 06-01-2015 C 35 07-01-2015 D 27 08-01-2015 D 28 ;

Re: Exporting a dataset to csv based on variable using macro. For example, 'City' in the example

Reeza — Wed, 12 May 2021 16:18:12 GMT

Why a macro? Why not use a dynamic filename using the FILEVAR option?

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lestmtsref/n15o12lpyoe4gfn1y1vcp6xs6966.htm#n0m1x5yncfgf4yn16d6ui4h6uinn

Example is here:
https://support.sas.com/kb/24/599.html

Re: Exporting a dataset to csv based on variable using macro. For example, 'City' in the example

ShashankB1987 — Wed, 12 May 2021 16:49:21 GMT

This is part of a bigger code and we have to be able to do this dynamically. Hence, I am trying to achieve this by macros. The dataset is just an example. So, it would very helpful if you can suggest steps to achieve this via macro. Thanks in advance.

Re: Exporting a dataset to csv based on variable using macro. For example, 'City' in the example

Tom — Wed, 12 May 2021 16:56:05 GMT

Please add some actual words to your question.

If you want a macro you need make some design decisions.

1) Figure out want code you want to run. Do you just want to use a data step to write the delimited file?

2) Figure out want varies from run to run. What options you want the user of the macro to have. What inputs they provide.

3) Figure out how to name the parameters.

4) Figure out how/when/where you are going to call the macro.

Re: Exporting a dataset to csv based on variable using macro. For example, 'City' in the example

ShashankB1987 — Wed, 12 May 2021 17:16:28 GMT

I am looking for a data step to write the delimited file.

Re: Exporting a dataset to csv based on variable using macro. For example, 'City' in the example

Reeza — Wed, 12 May 2021 17:31:22 GMT

Fully worked example:


data test;
  input color $ num;
datalines;
blue 1
blue 2
blue 3
green 4
green 5
red 6
red 7
red 8
;

proc sort data=test;by color;
run;


%macro split_file(file_name=, var_split=);
data _null_;
  length fname $30.;
  set &file_name;     
  by &var_split;                                               
  if first.&var_split then count+1;
  fname=cats("/home/fkhurshed/", &var_split ,".txt");
  file dummy filevar=fname;
  put color num count;
run;

%mend;

%split_file(file_name = test, var_split = color);

Log:

 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 68         
 69         
 70         data test;
 71           input color $ num;
 72         datalines;
 
 NOTE: The data set WORK.TEST has 8 observations and 2 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              546.68k
       OS Memory           23460.00k
       Timestamp           05/12/2021 05:30:35 PM
       Step Count                        24  Switch Count  2
       Page Faults                       0
       Page Reclaims                     151
       Page Swaps                        0
       Voluntary Context Switches        9
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 81         ;
 
 82         
 83         proc sort data=test;by color;
 84         run;
 
 NOTE: There were 8 observations read from the data set WORK.TEST.
 NOTE: The data set WORK.TEST has 8 observations and 2 variables.
 NOTE: PROCEDURE SORT used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              824.03k
       OS Memory           23720.00k
       Timestamp           05/12/2021 05:30:35 PM
       Step Count                        25  Switch Count  2
       Page Faults                       0
       Page Reclaims                     207
       Page Swaps                        0
       Voluntary Context Switches        9
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 
 85         
 86         
 87         %macro split_file(file_name=, var_split=);
 88         data _null_;
 89           length fname $30.;
 90           set &file_name;
 91           by &var_split;
 92           if first.&var_split then count+1;
 93           fname=cats("/home/fkhurshed/", &var_split ,".txt");
 94           file dummy filevar=fname;
 95           put color num count;
 96         run;
 97         
 98         %mend;
 99         
 100        %split_file(file_name = test, var_split = color);
 
 NOTE: The file DUMMY is:
       Filename=/home/fkhurshed/blue.txt,
       Owner Name=fkhurshed,Group Name=oda,
       Access Permission=-rw-r--r--,
       Last Modified=12May2021:11:30:35
 
 NOTE: The file DUMMY is:
       Filename=/home/fkhurshed/green.txt,
       Owner Name=fkhurshed,Group Name=oda,
       Access Permission=-rw-r--r--,
       Last Modified=12May2021:11:30:35
 
 NOTE: The file DUMMY is:
       Filename=/home/fkhurshed/red.txt,
       Owner Name=fkhurshed,Group Name=oda,
       Access Permission=-rw-r--r--,
       Last Modified=12May2021:11:30:35
 
 NOTE: 3 records were written to the file DUMMY.
       The minimum record length was 8.
       The maximum record length was 8.
 NOTE: 2 records were written to the file DUMMY.
       The minimum record length was 9.
       The maximum record length was 9.
 NOTE: 3 records were written to the file DUMMY.
       The minimum record length was 7.
       The maximum record length was 7.
 NOTE: There were 8 observations read from the data set WORK.TEST.
 NOTE: DATA statement used (Total process time):
       real time           0.02 seconds
       user cpu time       0.01 seconds
       system cpu time     0.00 seconds
       memory              614.25k
       OS Memory           23716.00k
       Timestamp           05/12/2021 05:30:35 PM
       Step Count                        26  Switch Count  0
       Page Faults                       0
       Page Reclaims                     141
       Page Swaps                        0
       Voluntary Context Switches        17
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           32

Re: Exporting a dataset to csv based on variable using macro. For example, 'City' in the example

Kurt_Bremser — Wed, 12 May 2021 17:34:10 GMT

@ShashankB1987 wrote:

I am looking for a data step to write the delimited file.

Then follow the links provided by @Reeza and use the FILEVAR= option.

Re: Exporting a dataset to csv based on variable using macro. For example, 'City' in the example

Kurt_Bremser — Thu, 13 May 2021 18:39:06 GMT

Please mark @Reeza 's post as solution.