Proc Tabulate+ods tagsets.excelxp +nested class variables

Ronein — Mon, 18 Feb 2019 07:38:01 GMT

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;

Re: Proc Tabulate+ods tagsets.excelxp +nested class variables

ChrisNZ — Tue, 19 Feb 2019 00:43:11 GMT

Thanks for supplying all the needed code.

How nice to be able to just paste and run!

This works from for me.

topic Proc Tabulate+ods tagsets.excelxp +nested class variables in SAS Programming

Proc Tabulate+ods tagsets.excelxp +nested class variables

Re: Proc Tabulate+ods tagsets.excelxp +nested class variables