Using PROC EXPORT to create an external file for each unique value of the BY group of the SAS data set

The sample data set, CANDY, contains the type of candy, the store name and number of bags sold. The SAS data set, CANDY is sorted by STORE and a new SAS data set is created called CANDY2 using PROC SORT. The new SAS data set, CANDY2, has only unique values of STORE. In a DATA _NULL_ step a macro variable is created for each STORE value and the total number of stores. Inside the macro is a DATA step and PROC EXPORT. In the DATA step a WHERE statement is used to subset the SAS data set based on STORE. Next the PROC EXPORT is used to create the external file based on STORE. Below is the sample data set and code to create 1 external file for each unique value of STORE.

data candy;                           
  input name $12.  +1 store $10.  bags; 
cards;                                 
Kisses       FastFare   500           
Reeses Cups  FastFare   2000          
Mr. Goodbar  FastFare   3000          
Milky Way    FastFare   1200          
Mars         FastFare   375           
Kisses       711        500           
Reeses Cups  711        2000          
Mr. Goodbar  Citgo       3000          
Milky Way    Citgo       1200          
Mars         Citgo       375           
;                                      
run;                                       
proc sort data=candy out=candy2 nodupkey;
by store;
run;

data _null_;
  set candy2 end=last;
  i+1;
  call symputx('store'||trim(left(put(i,8.))),store);
  if last then call symput('total', trim(left(put(i,8.))));
run;

%macro exportit;
   %do i=1 %to &total;
      data cust_&&store&i;
	set candy;
	where store="&&store&i";
      run;
      
      proc export data=cust_&&store&i outfile="c:\cust_&&store&i...csv" dbms=csv replace;
	  run;
   %end;
%mend exportit;

%exportit;

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Operating System and Release Information