How to Export Rows for Each Unique Value of Agent_Name

[yp2609]

For each unique value of Agent_Name, I want to export all relevant rows to an Excel file with the Agent's name in the filename. Can someone point me in the right direction? I'm using Base SAS 9.4

proc import datafile="\\columbia\dfsr1\Actuarial\Alice\Ohio PPA Agents\Filtered.csv"
    out=filter1_data
    dbms=csv
    replace;
run;

/* change the variable names */
data filter;
    retain agentname family_no unit_no zip effective_dt premC premP imp AnnPremC AnnPremP annImp imppct;
    set filter1_data
        (rename=(
            agentname = Agent_Name
			family_no = Family_Number
			unit_no = Unit_Number
            zip = Zip_Code
            effective_dt = Effective_Date
			premC = Single_Term_Current_Premium
			premP = Single_Term_Proposed_Premium
			imp = Single_Term_Impact_$
			AnnPremC = Annualized_Current_Premium
			AnnPremP = Annualized_Proposed_Premium
			annImp = Annualized_Impact
			imppct = Impact_Percent
        ));
run;
/* change variable orders */
proc sql;
    create table filter_2 as
    select 
        Agent_Name,
        Family_Number,
        Unit_Number,
        Zip_Code,
        Effective_Date,
		Single_Term_Current_Premium,
		Single_Term_Proposed_Premium,
		Single_Term_Impact,
		Annualized_Current_Premium,
		Annualized_Proposed_Premium,
		Annualized_Impact,
		Impact_Percent
    from filter
	where Agent_Name ne '';	
quit;

/* order dataset by Agent_Name */
proc sort data=filter_2 out=filter_2;
    by Agent_Name family_number unit_number;
run;

[Kathryn_SAS]

You can use CALL EXECUTE as described in this sample:

https://support.sas.com/techsup/notes/v8/26/140.html 

Sample code:

proc sort data=filter_2 out=filter_2;
    by Agent_Name family_number unit_number;
run;

%macro break(byval);                                                                                                                                  
   data &byval;                                                             
      set filter_2(where=(agent_name="&byval"));                              
   run;  

 ods _all_ close;
ods excel file="c:\temp\&byval..xlsx";
proc print data=&byval noobs;
run;
ods excel close;
 
%mend;                                                                      
                                                                                                                                                                                                                     
data _null_;                                                                
  set filter_2;                                                               
  by agent_name;                                                                  
  if first.agent_name then 
    call execute('%break('||trim(agent_name)||')');                
           
run;

[Tom]

Do you want an actual XLSX file?  Or do you instead just want to make a series of CSV files like you started with?

If the latter it is much easier since you can just use the FILEVAR= option on the FILE statement.

%let varnames=
 Agent_Name
 Family_Number
 Unit_Number
 Zip_Code
 Effective_Date
 Single_Term_Current_Premium
 Single_Term_Proposed_Premium
 Single_Term_Impact
 Annualized_Current_Premium
 Annualized_Proposed_Premium
 Annualized_Impact
 Impact_Percent
;
%let path=where ever you want to write the new csv files;

data _null_;
  set filter_2;
  by agent_name;
* Calculate filename to use ;
  filename=cats("&path/",agent_name,".csv");
  file csv dsd filevar=filename;
* Write header line when starting a new AGENT ;
  if first.agent_name then put "%sysfunc(translate(&varnames,%str(,),%str( )))";
* Write data line ;
  put &varnames;
run;

[Tom]

Why all that rigmarole to change the variable names and order?

Why not just read the CSV file yourself so you can control the names and order instead of forcing SAS to GUESS what is in the CSV file and so use names and order you don't want?

Something like:

data filter1_data;
  infile "\\columbia\dfsr1\Actuarial\Alice\Ohio PPA Agents\Filtered.csv"
    dsd truncover firstobs=2
  ;
* Define the variable type and storage lengths in the order you want;
  length ...... ;
* Now read in the variables in the order they appear in the CSV file ;
  input ..... ;
run;

[dxiao2017]

Hi @yp2609  have you come up with the solution? I think this is the very simple code you would like to use:

data have;
   input agent $ var1 $ var2 var3;
   datalines;
a a1 1 3
a a3 2 3
a a4 4 5
b b1 5 0
b b2 1 2
c c1 2 3
d d2 2 6
e e2 3 7
e e3 4 6
f f1 3 4
;
run;

proc sql;
select distinct agent
   into :agent1-
   from have;
quit;

libname want xlsx "&path/want.xlsx";
%macro split2excel;
%do i=1 %to &sqlobs;
   data want.&&agent&i;
      set have;
      if agent="&&agent&i";
   run;
%end;
%mend;
%split2excel;
libname want clear;

The excel file is created with each level of variable agent as a sheet name:

The log shows that the datasets and files are correctly created:

93         
 94         libname want xlsx "&path/want.xlsx";
 NOTE: Libref WANT was successfully assigned as follows: 
       Engine:        XLSX 
       Physical Name: ~/communitypost/want.xlsx
 95         %macro split2excel;
 96         %do i=1 %to &sqlobs;
 97            data want.&&agent&i;
 98               set have;
 99               if agent="&&agent&i";
 100           run;
 101        %end;
 102        %mend;
 103        %split2excel;
 
 NOTE: There were 10 observations read from the data set WORK.HAVE.
 NOTE: The data set WANT.a has 3 observations and 4 variables.
 NOTE: The export data set has 3 observations and 4 variables.