07-25-2016 12:32 PM
I am trying to create individual spreadsheets grouped by site.
Each spreadsheet will have multiple tabs ranging from 1 - 4 depending on the data.
I am using a macro to do this since there are 837 individual combinations of site and measure. I have gotten the code to generate 837 individual spreadsheets for all of the different combinations (which I've listed below), but I can't seem to figure out how to get it to create ONE excel workbook for each site with tabs for the individual measures.
Any assistance is greatly appreciated! Thanks!
%do i=1 %to 10/*&Iteration*/;
proc sql; select 'Site ID'n into iteID from table2 where obs = &i;quit;
proc sql; select MSRID into :MsrID from table2 where obs = &i;quit;
proc sql; select SiteName into iteName from table2 where obs = &i;quit;
proc sql; select Title into :Title from table2 where obs = &i;quit;
file="C:.....\&SiteID &MsrID &SYSDATE..xml"
style=sansprinter /*Styles to control appearance of output*/
options (Embedded_titles = 'yes'
Embedded_Footnotes = 'yes'
sheet_name= "&SiteID &MsrID"
PAGEBREAKS = 'yes'
row_repeat = '1-5'
frozen_rowheaders = 'yes'
convert_percentages = 'yes'
Center_Horizontal = 'yes'
Print_footer = 'Created on &D Page &P of &N'
title1 j=center "&SiteName";
title2 j=center "Site ID: &SiteID";
proc report data=combine nowd headline;
where 'Site ID'n = put(&SiteID,3.) and 'Measure ID'n = "&MsrID";
OPTIONS TopMargin = .50in
column 'Site ID'n 'Measure ID'n 'Member Name'n 'Member ID'n 'Date of Birth'n 'PCP name'n SiteName;
define 'Site ID'n / noprint;
define 'Measure ID'n / noprint;
define SiteName / noprint;
define 'Member Name'n / left "Member Name" width=20;
define 'Member ID'n / center "Member ID" width=11;
define 'Date of Birth'n / center "Member DOB" width=9;
define 'PCP name'n / left "PCP Name" width=15;
ods _all_ close;
07-25-2016 04:09 PM - edited 07-26-2016 08:42 AM
It looks to me like your where is only selecting on unique combinations of Site and Measure. I would recommend doing each SITE separately and then using BY Measure to get a new sheet for every measure. For example, I know that this data is not structured exactly like yours, but I have generated a separate workbook for each product (Boot, Slipper, etc) with a separate sheet for each region. My regions did not change for each product, so each of my workbooks had the same names for the sheets (initially, which is why I had the pre-processing step to change the data for each workbook). With BY group processing, though, if you thought of my PRODUCT as being like your SITE and then my REGION as being like your Measure ID, if you adopted something similar to this, then each Measure ID would get a separate sheet.
My macro is not data driven like your macro. But that part is a minor part of the example. The major part of the example, is getting it to work for the one worksheet for the main group and then using BY group processing to get a separate sheet for every "subgroup".
Here's the code:
** make 2 unique products with 4 regions so there are 4 sheets to each product; proc sort data=sashelp.shoes out=shoes; where region in ('Asia', 'Pacific', 'Western Europe', 'Canada') and product in ('Boot', 'Slipper'); by product region; run; ** make some fake regions so that the regions for Slipper are different; data newshoes; set shoes; by product; if product = 'Slipper' then do; if region = 'Asia' then region = 'Apex'; else if region = 'Pacific' then region = 'Penton'; else if region = 'Western Europe' then region = 'Westeros'; else if region = 'Canada' then region = 'Chancelor'; end; title; %macro dorept(product=); ods tagsets.excelxp file="c:\temp\Shoes_&product..xml" style=htmlblue options(sheet_interval='bygroup' sheet_name='Region_#byval1' suppress_bylines='yes' embedded_titles='yes'); proc report data=newshoes; title "Wanted &Product"; where product = "&product"; by region; run; ods tagsets.excelxp close; %mend dorept; %dorept(Product=Boot) %dorept(Product=Slipper)
And the output:
08-01-2016 02:35 PM
Thank you! I was able to get the code to work to create the multiple tabs per spreadsheet..... my problem now lies with getting them each to have their own title. I will post on that one later after I dig around. THANKS AGAIN!
08-01-2016 02:55 PM - edited 08-01-2016 02:56 PM
I guess I don't understand what you mean by "getting them to each have their own title" Do you mean each sheet has a separate title? My TITLE1 shows the product value, you can easily get a TITLE2 to show on each sheet that shows the region.