/*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
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.
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.
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: