Print to Excel with multiple sheets from a macro

[dbjosiah]

Hi, I have a macro which cycles through 58 California counties. I would like to create a single Excel workbook where each county's output has a separate tab. This would be simple enough except that there are 220 records per county and I don't want to have all that print to the regular SAS output in the process. Much to my surprise, after only about an hour searching through existing help questions and google pages, I managed to create what I want with the following code:

%let counties=California Alameda ... Yuba;
DATA _null_;
  if _n_=1 then do;
    set one;
	numcnts=countw(symget('counties'));
	call symputx('numcounties',numcnts,'g');
  end;
run;

%macro geo;
ods _all_ close;
ods tagsets.ExcelXP path="&path" file="Longitudinal vaccine data.xml";
  %do i=1 %to &numcounties;
    %let unit=%scan(&counties, &i);
%put geographical unit=&unit;
DATA &unit;
  set one;
  geounit=symget('unit');
  if county=geounit;
run;
if &i=1 then 
ods excel file="&path.&unit longitudinal vaccine data, %sysfunc(trim(%qsysfunc(left(%qsysfunc(date(),worddate.))))).xlsx"
  options(sheet_name="&unit");
  else if &i>1 then ods excel options(sheet_name="unit");
PROC PRINT data=&unit; var county--doses1p; run;
ods excel close;
%end;
ods tagsets.ExcelXP close;
%mend;
%geo;
ods listing;

However, while it successfully creates the Excel workbook without writing to the output window, it also puts the following error messages in the log (showing the first loop only):

To be clear, I'm happy with the product, just wondering what the error issue is.

Thanks!

[jimbarbour · Accepted solution]

I've seen messages like that before. The messages aren't particularly helpful. I guess if it's working, don't get too excited about it.

Jim

[SASKiwi]

This isn't correct macro code:

if &i=1 then 
ods excel file="&path.&unit longitudinal vaccine data, %sysfunc(trim(%qsysfunc(left(%qsysfunc(date(),worddate.))))).xlsx"
  options(sheet_name="&unit");
  else if &i>1 then ods excel options(sheet_name="unit");

Possibly this might work better:

%if &i=1 %then %do; 
ods excel file="&path.&unit longitudinal vaccine data, %sysfunc(trim(%qsysfunc(left(%qsysfunc(date(),worddate.))))).xlsx"
  options(sheet_name="&unit");
%end;
%else %if &i>1 %then %do;
ods excel options(sheet_name="unit");
%end;

[Tom]

Turn on the MPRINT option so you can see the SAS code that your macro is generating.  That will make the error messages much clearer.

This first step before the macro definition does not make much sense.  Why are you referencing the dataset? The code does not use the data at all.  Just simplify to:

data _null_;
  numcnts=countw(symget('counties'));
  call symputx('numcounties',numcnts,'g');
run;

And the macro code is confusing.  Your macro is generating a IF statement outside of any data step (or proc that allows that statement).  Did you intend to use macro %IF statement to conditionally generate different blocks of SAS code instead?

But then what is the different SAS code that you intend to generate?

Why are you opening both an TAGSETS.EXCELXP output destination and an EXCEL destination?

Do you want one file with all sheets and a series of separate files with one sheet each?

Is there any reason why one of them needs be and XML file instead of an XLSX file?

Why do you have an extra DATA step that is just subsetting the data? Why not just add a WHERE statement to the PROC PRINT step?

[ChrisNZ]

I must have missed something, I don't understand why such complex code.

Why not this?

%let counties=California Alameda ... Yuba;

%macro geo;
  %local i county;
  ods _all_ close;
  ods excel file="&path.&unit longitudinal vaccine data, %qsysfunc(strip(%qsysfunc(date(),worddate.))).xlsx";
  %do i=1 %to %sysfunc(countw(&counties));
    %let county=%scan(&counties, &i);
    %put geographical &=county;
    ods excel options(sheet_name="&county");
    proc print data=ONE ; where COUNTY="&county"; var COUNTY--DOSES1P; run;
  %end;
  ods excel close;
%mend;

Also, I generally use this for my time stamps

%let timestamp=%sysfunc(translate( %sysfunc(datetime(),timetamp.),-,:));

as the files are naturally sorted by date when sorted by name.

[dbjosiah]

This worked great! Not sure how/where to incorporate your timestamp though.

[jimbarbour]

---

@dbjosiah wrote:
This worked great! Not sure how/where to incorporate your timestamp though.

---

I believe @ChrisNZ is referring to the filename for your Excel file.  Putting a date stamp into the file name in the format that @ChrisNZ suggests will automatically put the files in order (and guarantee unique file names of course).

Jim

[dbjosiah]

Oh, ok, thanks. I got it to work, but since I only organize these by week, I don't really need unique names.

[Tom]

You can also just allow the PROC and ODS EXCEL to generate separate sheets for each value of COUNTY.  https://blogs.sas.com/content/sastraining/2017/04/18/control-name-excel-sheets-created/

So no macro needed.

%let counties=California Alameda ... Yuba;
ods excel file="&path.Longitudinal vaccine data by county %sysfunc(date(),yymmdd10.).xlsx";
ods excel options(sheet_interval='bygroup' sheet_name='#byval1');
options nobyline;
proc print data=ONE;
  where findw("&counties",county,' ','t');
  by county;
  var COUNTY--DOSES1P;
run;
options byline;
ods excel close;

[dbjosiah]

Wow Tom, that's great too! But I've learned so much from the other macro solutions... 🙂
Thanks everyone. I'll credit jimbarbour with the Solution since he answered my actual question, but all of the suggestions are greatly appreciated.

[andreas_lds]

Why are you using the outdated tagset.excelxp and not "excel"? Both have an option to create multiple sheets:

proc sort data=sashelp.class out=work.class;
  by Sex;
run;

options nobyline;

ods excel file="&benutzer\temp\class.xlsx" 
   options(sheet_interval='bygroup' sheet_name="#byval1");

proc print data=work.class noobs;
   by Sex;
run;

ods excel close;