The SAS Output Delivery System and reporting techniques

Exporting 105 SAS data tables into the same Excel sheet?

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

Exporting 105 SAS data tables into the same Excel sheet?

/*Hi Forum,

 

I am having over 105 SAS datasets.

I have provided below just two datasets to comprehend my question to external readers only.

 

/*EXAMPLE TABLE 1*/

/*Income variable**/

data Have_1;

input Income;

cards;

0

100

105

100

200

300

300

400

500

1000

;

run;

 

/*I Define a proc foramt*/

proc format ;

value for_income

. = 'Missing'

low-< 0 = '<0'

0 = '0'

0<- 6 = '>0 - 6'

6 <- 12 = '>6 - 12'

12 <- 60 = '>12 - 60'

60 <- 120 = '>60 - 120'

120 <- 240 = '>120 - 240'

240 <- 360 = '>240 - 360'

360< - high = '>360';

run;

 

/*I Apply the proc format for "income" variable and generate a sas data table called "want".*/

proc freq data=Have_1 noprint;

tables income/list missing out=want;

format income for_income.;

run;

 

/*Exporting table "want" to Excel*/

proc export data=want

outfile= "\\xxxx\xxx\Final.xls"

dbms=excelcs replace label;

sheet="Sheet1";

run;

 

/*EXAMPLE TABLE 2*/

/*Distance variable*/

data Have_2;

input Distance;

cards;

0

.

105102

1000

199

30125

302

400

50000

1000

125

305

1200

;

run;

 

/*Define a different proc foramt*/

proc format ;

value for_distance

. = 'Missing'

low-< 0 = '<0'

0 = '0'

0 <- 100 = '>0 - 100'

100 <- 1000 = '>100 - 1000'

1000 <- 10000 = '>1000 - 10000'

10000 <- 50000 = '>10000 - 50000'

50000 <- 100000 = '>50000 - 100000'

100000 <- 200000 = '>100000 - 200000'

200000 <- High = '>200000';

run;

 

/*I Apply the proc format for "distance" variable and generate a sas data table with same name called "want".*/

proc freq data=Have_2 noprint;

tables distance/list missing out=want;

format distance for_distance.;

run;

 

/*Exporting table "want" to Excel*/

proc export data=want

outfile= "\\xxxx\xxx\Final.xls"

dbms=excelcs replace label;

sheet="Sheet1";

run;

/*Q:

I want to export all the 105 SAS datasets, each called "want" into a single Excel tab so that all 105 tables stack

one after the other like below. Could you help me?*/

 

 

Income COUNT PERCENT

0                       1     10

>60 - 120   3      30

>120 - 240 1     10

>240 - 360 2      20

>360                3      30

 

 

Distance                      COUNT PERCENT

.                                      1             7

0                                      1             7

>100 - 1000             7             53

>1000 - 10000        1             7

>10000 - 50000      2            15

>100000 - 200000 1             7


Accepted Solutions
Solution
‎09-07-2017 10:01 AM
Super User
Posts: 20,730

Re: Exporting 105 SAS data tables into the same Excel sheet?

1. Create all your datasets and formats first.

2. Use ODS EXCEL or ODS TAGSETS.EXCELXP to export your data, using the sheet_interval option so everything is on one page. 

 

Here's a link to several basic examples:

https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html

 

The first is what you're trying to emulate. 

 

If all your output data sets have the same format, maybe a single PROC FREQ, clean up the output and export once? Create a view with the formats applied and run a single PROC FREQ at the end? If you're using multiple input data sets this does not apply.

 

Here's an example of how that may look:

https://gist.github.com/statgeek/e0903d269d4a71316a4e

 

The you print the single table. 

 

View solution in original post


All Replies
Solution
‎09-07-2017 10:01 AM
Super User
Posts: 20,730

Re: Exporting 105 SAS data tables into the same Excel sheet?

1. Create all your datasets and formats first.

2. Use ODS EXCEL or ODS TAGSETS.EXCELXP to export your data, using the sheet_interval option so everything is on one page. 

 

Here's a link to several basic examples:

https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html

 

The first is what you're trying to emulate. 

 

If all your output data sets have the same format, maybe a single PROC FREQ, clean up the output and export once? Create a view with the formats applied and run a single PROC FREQ at the end? If you're using multiple input data sets this does not apply.

 

Here's an example of how that may look:

https://gist.github.com/statgeek/e0903d269d4a71316a4e

 

The you print the single table. 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 182 views
  • 0 likes
  • 2 in conversation