BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Aidan
Quartz | Level 8

Hi there

 

What I am looking to do is dynamically populate an excel file.

So below I can get the info per period as per page statement.

 

Ideally I would have an excel file with the periods split into each tab.

I have tried a few things with ODS excel but no luck as yet, would anyone know of any code available that I could try and integrate that with my current code?

 

My code is below;

 

PROC TABULATE
DATA=CMTEST.A2

;

VAR ARTR_AMOUNT_BILLED;
CLASS FREQUENCY / ORDER=UNFORMATTED MISSING;
CLASS ADVANCED_HEADING / ORDER=UNFORMATTED MISSING;
CLASS AGE_BAND / ORDER=UNFORMATTED MISSING;
CLASS PERIOD / ORDER=UNFORMATTED MISSING;
TABLE
/* PAGE Statement */
all = 'Total'
PERIOD ,
/* ROW Statement */
AGE_BAND=' '
all = 'Total' ,
/* COLUMN Statement */
ADVANCED_HEADING=' ' *FREQUENCY=' ' *(ARTR_AMOUNT_BILLED=' ' * Sum=' ' )
all = 'Total' *(ARTR_AMOUNT_BILLED=' ' * Sum=' ' ) ;
;

RUN;

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

If you set period as a by group in the proc tabulate procedure, then use ods tagsets.excelxp, there is an option to set bygroup as sheet interval:

https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_help.html

 

This should put each by group on a new sheet.

View solution in original post

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

If you set period as a by group in the proc tabulate procedure, then use ods tagsets.excelxp, there is an option to set bygroup as sheet interval:

https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_help.html

 

This should put each by group on a new sheet.

Cynthia_sas
SAS Super FREQ

Hi:

  If you want to control the sheet names with the BY group values, then using BY group processing will work. You won't get the ALL table with BY group processing though. So you'd have to work around that. I found if I used ODS EXCEL and sheet_interval='table' that I got 5 sheets for 4 periods -- a total sheet and then 1 sheet for each period.

 

  Here's the code I tried with some dummy data:

data testit;
  set sashelp.prdsale;
  where country in ('CANADA' 'GERMANY');
  artr_amount_billed = sum(actual, predict/10);
  if division = 'CONSUMER' then frequency = 'WEEKLY ';
  else frequency = 'MONTHLY';
  if country = 'CANADA' then advanced_heading = 'Adv 1';
  else advanced_heading = 'Adv 2';
  if prodtype = 'FURNITURE' then age_band = 'CHILD';
  else if prodtype = 'OFFICE' then age_band='ADULT';
  period = quarter;
run;
 
ods excel file='c:\temp\use_xl.xlsx'
    options(sheet_interval='table');
 
PROC TABULATE DATA=testit;
VAR ARTR_AMOUNT_BILLED;
CLASS FREQUENCY / ORDER=UNFORMATTED MISSING;
CLASS ADVANCED_HEADING / ORDER=UNFORMATTED MISSING;
CLASS AGE_BAND / ORDER=UNFORMATTED MISSING;
CLASS PERIOD / ORDER=UNFORMATTED MISSING;
TABLE all = 'Total' PERIOD ,
      AGE_BAND=' ' all = 'Total' ,
      ADVANCED_HEADING=' ' *FREQUENCY=' ' *(ARTR_AMOUNT_BILLED=' ' * Sum=' ' )
      all = 'Total' *(ARTR_AMOUNT_BILLED=' ' * Sum=' ' ) ;
 RUN;
 ods _all_ close;

Hope this helps,

cynthia

Aidan
Quartz | Level 8
Thanks both for your input, both work fine for me

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 8316 views
  • 2 likes
  • 3 in conversation