How to export SAS dataset to XLSX, by category, using macro variable?

Question

I have a large data set that has approximately 100 names of managers. Now, I need to export the data by manager name so that I have a single dataset for each manager. I am able to use a macro to create a separate dataset for each manager (essentially, a category) using the code:

%macro break(byval);                                                                                                                                  
   data &byval;                                                             
      set final(where=(Project_Manager_Name="&byval"));                              
   run;                                                                                                                                                 
%mend;                                                                      

data _null_;                                                                
  set final;                                                               
  by Project_Manager_Name;                                                                  
  if first.Project_Manager_Name then 
    call execute(%nrstr('%break('||trim(Project_Manager_Name)||')'));            
 run;

This is where I get stuck. I just need .xlsx files of each and include the name of the manager at the end of each file name, like:

proc export
    data = final
    dbms = xlsx
    outfile = "&OUTPUT.\Final_Report_ManagerName.xlsx"
    replace;
run;

I assume I put the &byval macro variable somewhere in the outfile name, but I'm still getting errors that it's not being reference. Any insight?

Move your proc export into your break macro, replace by all with manager name in outfile. — Reeza

Joe Joe · Accepted Answer · 2014-10-10T19:10:59

To use PROC EXPORT you'll have to write a macro and call PROC EXPORT 100 times. This isn't a fantastic method. You could also use LIBNAME to do the same thing, but instead of 100 export calls, you have 100 dataset names on your data step and 100 select/when/output conditional blocks identifying what to do with each manager's data.

Better ways:

If you have 9.4M1, you can use ODS EXCEL to do this. Look up ODS EXCEL, and then you have it create a new sheet for each by group using sheet_label option to define the sheet name from a variable. This option is the same as ODS TAGSETS.EXCELXP (as is most of ODS EXCEL).
If you don't, you could use a hash object output method to do this a bit more efficiently, though still not ideally - at least it wouldn't require a ton of macro programming.

At the end of the day, creating an export macro and calling it similarly to how you call the %break macro (or, more likely, setting the export code inside the %break macro) is probably the easiest thing for you to do, even though it's not really ideal (very long run time for what you're doing).

proc export
    data = &byval.
    dbms = xlsx
    outfile = "&OUTPUT.\Final_Report_&byval..xlsx"
    replace;
run;

That needs to go inside the %break macro to do this. You can also skip the data step in the byval and just add a WHERE to the export, data=final(where=(Project_Manager_Name="&byval")).

How to export SAS dataset to XLSX, by category, using macro variable?

1 Answers