How to create a single file with multiple tabs from tables created from a Macro

[mary_mcneill]

Hello!

I am looking for help on a SAS program I have where I run multiple Proc Tabulate analysis for select counties via a macro. I would like to have all proc tabulate outputs for each county in an individual tab named as the county name. Currently it outputs to one tab and it is a lot of data to scroll through all the counties together. Sample data attached.

options orientation=portrait missing=0;
ods listing close;
ods html path="&path" file="&file";

%macro desc(location);

/*YTD TOTALS - COUNTY REPORTS*/

title1 "Cases of Disease in &location County, Year Total";

proc tabulate data=work.disease2 format=5.;

where DiagAddress_County = "&location";

format sex $sex. evyear evyear. age agegrp. ;

class evyear age sex / missing preloadfmt;

tables evyear=' '*age=' ' all*evyear=' ',sex=' ' all/printmiss box='Age' rts=25;

keylabel n=' ' all='Total';

run;

proc tabulate data=work.disease2 format=5.;

where DiagAddress_County = "&location";

format sex $sex. evyear evyear. race2 $race. ;

class evyear race2 sex / missing preloadfmt;

tables evyear=' '*race2=' ' all*evyear=' ',sex=' ' all/printmiss box='Race' rts=25;

keylabel n=' ' all='Total';

run;

proc tabulate data=work.disease2 format=5.;

where DiagAddress_County = "&location";

format sex $sex. evyear evyear. ethnicity2 $ethnic.;

class evyear ethnicity2 sex / missing preloadfmt;

tables evyear=' '*ethnicity2=' ' all*evyear=' ',sex=' ' all/printmiss box='Ethnicity' rts=25;

keylabel n=' ' all='Total';

run;

proc tabulate data=work.disease2 format=5.;

where DiagAddress_County = "&location";

format sex $sex. evyear evyear. FacilityType $prvtype.;

class evyear sex / missing preloadfmt;

class FacilityType / order=formatted missing preloadfmt;

tables evyear=' '*FacilityType=' ' all*evyear=' ',sex=' ' all/printmiss box='FacilityType' rts=25;

keylabel n=' ' all='Total';

run;

%mend;
%desc(CountyA);
%desc(CountyG);
%desc(CountyZ);

[PaigeMiller · Accepted solution]

Use a BY statement, not a macro.

You can create the desired Excel tabs, named by the BY variable, with ODS EXCEL and certain options such as SHEET_INTERVAL='BYGROUP' and SHEET_LABEL='byvariablename'

Example: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/odsug/p09n5pw9ol0897n1qe04zeur27rv.htm#n0bmw0...

[mary_mcneill]

Thanks! I am using a macro to run the same multiple proc tables on only 3 counties out of 88 counties total in my dataset so I thought I would maybe be able to use the same location macro I indicated for the 3 counties to also output the multiple analysis for each county to its own tab in Excel opposed to it outputting all the analysis for the three counties in one tab. I am assuming if I use a By Group with the data set, I will get 88 Tabs, one for each county instead of just the 3 I want, but I'll look into that more 🙂 I could also possibly just create a new dataset containing only the counties I need the analysis on, and then it might work for just the 3 counties I need it for 🙂

[mary_mcneill]

Just for reference, I am running 67 Proc tabulates for each of the 3 counties which was why I did it with a macro 🙂

[PaigeMiller]

You can have more than one TABLE statement in a PROC TABULATE. So it may be possible to have one call to PROC TABULATE produce all of your three desired tables (although I haven't gone through your code carefully enough to be 100% convinced of that), and then the BY variable approach should work.

[mary_mcneill]

I was able to consolidate the 67 proc statements into two by using multiple Table statements (this SAS program was inherited and needed a good updating!). Unfortunately, I cannot put them all into one because the parameters shift for the other proc statement (the first one looks at the entire year for two years of data and the other looks at a specific timeframe of each year i.e., month-month). I was able to utilize the By Group instead of the macro and it is giving me results for all 3 counties on separate sheets using the code you provided; however, since I have 2 proc statements now, it is giving me 6 sheets (3 for the first proc statement and 3 for the second for each county). Do you know if there is a way for me to get all results from both proc statements to produce 3 sheets instead of 6? 

Also, is there a way to supress ""byvariablename - " from the sheet name when using SHEET_LABEL='byvariablename'

[PaigeMiller]

Do you know if there is a way for me to get all results from both proc statements to produce 3 sheets instead of 6? 

I'm guessing this takes us back to the macro solution.

[ballardw]

What do you mean by "multiple tabs" with an ODS HTML output? HTML typically is a single page.

If you were sending the output to a spreadsheet, such as with ODS EXCEL, no macro would be needed. You could use BY group processing and an option on the ODS EXCEL statement would name the sheets using the BY values though that would require sorting the data by the variable.

You may also want to consider running this non-macro code and comparing the results:

proc tabulate data=work.disease2 format=5.;
format sex $sex. evyear evyear. age agegrp. ;
class evyear age sex / missing preloadfmt;
class DiagAddress_County;
tables DiagAddress_County='Cases of Disease in' ,
      evyear=' '*age=' ' all*evyear=' ',
      sex=' ' all/printmiss box='Age' rts=25;
keylabel n=' ' all='Total';
run;

I find it helpful to have each dimension of a proc tabulate table on a separate line and indent long dimensions. Above the DiagAddress_County would be a PAGE dimension. If you send the output to ODS EXCEL and use the option sheet_interval='Table' then each table would appear on a separate tab.

[Ksharp]

Here is an example. You can start with it. Good Luck.

ods _all_ close;

ods excel file="C:\temp\ABC12.xlsx" options(sheet_name="Sh1" sheet_interval="none");
title 'Table1';
proc print data=sashelp.class(obs=3) noobs;
run;
title 'Table2';
proc print data=sashelp.shoes(obs=3) noobs;
run;

ods excel options(sheet_name="Sh2" sheet_interval="now"); /*This tells SAS to start a new sheet for next PROCs*/
proc print data=sashelp.cars(obs=5);
run;
title 'Table2';
proc print data=sashelp.shoes(obs=3) noobs;
run;
ods excel close;

[mary_mcneill]

Just for reference, I am running 67 Proc tabulates for each of the 3 counties (out of 888 total in my dataset) which was why I did it with a macro. If I did it this way, I think I would have to repeat all 67 proc tabulates to indicate when to start a new tab. I was hoping there was a way to utilize the macro (which specifies County=) to tell SAS to run all 67 proc tabulates on the first county and then when it runs it again with the new county, it would output all 67 for the second county in a new tab.

[ballardw]

---

@mary_mcneill wrote:
Just for reference, I am running 67 Proc tabulates for each of the 3 counties (out of 888 total in my dataset) which was why I did it with a macro. If I did it this way, I think I would have to repeat all 67 proc tabulates to indicate when to start a new tab. I was hoping there was a way to utilize the macro (which specifies County=) to tell SAS to run all 67 proc tabulates on the first county and then when it runs it again with the new county, it would output all 67 for the second county in a new tab.

---

In the macro you would be adding two ODS EXCEL options. One to change the sheet interval to something like 'Proc" then another to 'none' (so all the following output is on the same tab) and at that point you could add a SHEET_NAME="&location" (assuming &location is a valid tab name).

Note that if your 3 tabulates all use the same data set, do not have a variable that changes from Class to Var or vice versa you could use ONE tabulate with 3 Table statements. At which point I would also point out that BY would likely be preferable to WHERE as you could use the Sheet_interval='bygroup'.

[mary_mcneill]

I am in the process of consolidating the proc tabulates that I can; however, since I do have some parameters that change, I am unable to include all 67 tables into one 🙂 I will try adding the additional ODS EXCEL option