Proc Tabulate to excel

Accepted Solution Solved
Reply
Contributor
Posts: 64
Accepted Solution

Proc Tabulate to excel

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;


Accepted Solutions
Solution
‎10-19-2017 08:00 AM
Super User
Super User
Posts: 9,376

Re: Proc Tabulate to excel

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


All Replies
Solution
‎10-19-2017 08:00 AM
Super User
Super User
Posts: 9,376

Re: Proc Tabulate to excel

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.

SAS Super FREQ
Posts: 9,310

Re: Proc Tabulate to excel

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

Contributor
Posts: 64

Re: Proc Tabulate to excel

Posted in reply to Cynthia_sas
Thanks both for your input, both work fine for me
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 311 views
  • 2 likes
  • 3 in conversation