Hello
I create a summary table via Proc Tabulate.
I am using ods tagsets.excelxp to export it to excel.
In the result I want that region and division class variables will be nested ( instead of placing them next to each other)
Why in the excel file that was created I see that region and division are next to each other and not nested?
Can you provide a code to solve the problem please?
Data Rawtbl;
input ID 1-2 Region$ 4-12 Division$ 13-29 Type 31 expenditures 33-37;
cards;
1 Northeast New England 1 10
2 Northeast Middle Attlantic 1 20
3 Northeast Middle Attlantic 2 30
4 Northeast Middle Attlantic 2 40
5 Northeast New England 2 50
6 West Mountain 1 50
7 West Mountain 1 60
8 West Mountain 2 70
9 West Pacific 2 80
10 West Pacific 1 90
11 West Pacific 2 100
;
Run;
proc format;
value usetypeFmt
1='Residential customer'
2='Business customer';
Run;
ods tagsets.ExcelXP file="/Path/DavidY1.xls"
style=Printer
options(sheet_name="G1"
absolute_column_width="10"
sheet_interval='NONE'
embedded_titles='yes'
embedded_footnotes='yes') ;
options nodate pageno=1 linesize=80 pagesize=60;
proc tabulate data=Rawtbl format=dollar12. noseps;
class region division type;
var expenditures;
table region*division,
type='Customer Type'*expenditures=' '*sum=' '/ rts=25 indent=4;
format type usetype.;
title 'Energy Expenditures for Each Region';
title2 '(millions of dollars)';
run;
ods tagsets.excelxp close;
Thanks for supplying all the needed code.
How nice to be able to just paste and run!
This works from for me.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.