Proc Tabulate to excel

[Aidan]

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;

[RW9 · Accepted solution]

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]

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]

Thanks both for your input, both work fine for me