CODEWITHSUNDEEP
csvsas
user1006991
user1006991

Reputation: 25

PC SAS: ODS CSV to sheets

Morning all,

I have 4 proc tabulates, which i want to ouput to one workbook, but different sheets automatically. How can I do this.

Attached is the sample of the proc tabs.

ods csv;
proc tabulate data=test1 noseps missing; 
class Router_Desc Team_Desc router team;
var DaysDelq;
table Router_Desc*Team_Desc*router*team*ENF_LTR*SOLS_LTR ALL, DaysDelq=' '*(N=VOL    SUM=VALUE);
where ENF_LTR='Y' and SOLS_LTR='Y';
run;

proc tabulate data=test1 noseps missing; 
class Router_Desc Team_Desc router team;
var DaysDelq;
table Router_Desc*Team_Desc*router*team*ENF_LTR*SOLS_LTR ALL, DaysDelq=' '*(N=VOL     SUM=VALUE);
where ENF_LTR='Y' and SOLS_LTR='N';
run;

proc tabulate data=test1 noseps missing; 
class Router_Desc Team_Desc router team;
var DaysDelq;
table Router_Desc*Team_Desc*router*team*ENF_LTR*SOLS_LTR ALL, DaysDelq=' '*(N=VOL     SUM=VALUE);
where ENF_LTR='N' and SOLS_LTR='Y';
run;

proc tabulate data=test1 noseps missing; 
class Router_Desc Team_Desc router team;
var DaysDelq;
table Router_Desc*Team_Desc*router*team*ENF_LTR*SOLS_LTR ALL, DaysDelq=' '*(N=VOL     SUM=VALUE);
where ENF_LTR='N' and SOLS_LTR='N';
run;
ods csv close;

Upvotes: 0

Views: 1246

Answers (2)

DataParadigms
DataParadigms

Reputation: 437

Like Carolina Jay said, look at the ODS XP options, you can do a lot with them. The following skeleton should at least get you up and running.

Create the workbook. 

ods tagsets.excelxp file="yourfile.xls"
options(orientation='Landscape');

Run something like the following

ods tagsets.ExcelXP options(sheet_name="test1");
proc tabulate...
ods tagsets.ExcelXP options(sheet_name="test2");
proc tabulate...
ods tagsets.ExcelXP options(sheet_name="test3");
proc tabulate...
ods tagsets.ExcelXP options(sheet_name="test4");
proc tabulate...

Don't forget to close it all with a 

ods tagsets.excelxp close;

All of ODS options are in the quick reference. You can get more control of the output by doing your tabulates outside of the ods and then using proc report within the ods to take advantage of capabilities proc report gives you.

Upvotes: 1

Jay Corbett
Jay Corbett

Reputation: 28411

With ODS there is a tagset written for Excel output. It generates XML which Excel understands. Each proc can be written to a different worksheet. Try these links.

ExcelXP Tagset DEMO

ExcelXP options

This paper includes an explanation of how to download and install the tagset

Upvotes: 2

Related Questions