topic Macros and Proc Report to create an excel report in ODS and Base Reporting

Macros and Proc Report to create an excel report

shopkins — Mon, 25 Jul 2016 16:32:52 GMT

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;

Re: Macros and Proc Report to create an excel report

Cynthia_sas — Tue, 26 Jul 2016 12:42:25 GMT

Hi:

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

cynthia

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:

Re: Macros and Proc Report to create an excel report

shopkins — Mon, 01 Aug 2016 18:35:09 GMT

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!

Re: Macros and Proc Report to create an excel report

Cynthia_sas — Mon, 01 Aug 2016 18:56:22 GMT

Hi,

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.

cynthia