Hello! 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! %Macro SiteReports; %do i=1 %to 10/*&Iteration*/; proc sql; select 'Site ID'n into :SiteID from table2 where obs = &i;quit; proc sql; select MSRID into :MsrID from table2 where obs = &i;quit; proc sql; select SiteName into :SiteName from table2 where obs = &i;quit; proc sql; select Title into :Title from table2 where obs = &i;quit; ods tagsets.ExcelXP 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_headers= 'yes' frozen_rowheaders = 'yes' autofit_height= 'yes' autofit_width= '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"; title3 j=center"&Title"; proc report data=combine nowd headline; where 'Site ID'n = put(&SiteID,3.) and 'Measure ID'n = "&MsrID"; OPTIONS TopMargin = .50in Bottommargin=0.5in Leftmargin=0.7in Rightmargin=0.7in; 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; run; ods _all_ close; %end; %MEND SiteReports; %SiteReports;
... View more