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?

Answer 1

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")).