BookmarkSubscribeRSS Feed
shopkins
Calcite | Level 5

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;

 

 

 

3 REPLIES 3
Cynthia_sas
SAS Super FREQ

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:

two_products_4_regions.png

shopkins
Calcite | Level 5

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!

Cynthia_sas
SAS Super FREQ

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

 

byval.png

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1263 views
  • 0 likes
  • 2 in conversation