Exporting 105 SAS data tables into the same Excel sheet?

[dunga]

/*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

[Reeza · Accepted solution]

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.