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;
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.
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.
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
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.