BookmarkSubscribeRSS Feed
chennupriya
Quartz | Level 8

Hi ,

I have 800 datasets and i want to export all the datasets into one sheet and not multiple sheet and each dataset has two varaibles

 

dataset A

count_id  rate

12          0.12

13         0.13

 

dataset B

count_id rate

15      0.11

16      0.22

 

i want both these datasets to write to one excel sheet

 

Can anyone pls help

 

Thanks

2 REPLIES 2
Haikuo
Onyx | Level 15

Am I the only one at work today Smiley Very Happy

 

Well you can two approaches,

1.  union/stack/append your sas tables first, if you don't like the intermedian table, you can opt for sas views, then output to Excel.

2. Do it on the fly, if you have SAS/ACCESS to pc files.

 

My gut tells me that you are asking for the #2, so here it goes:

 


LIBNAME OUT XLSX "/your output folder/want.XLSX";

DATA OUT.want;
SET table1 table2 .....;
RUN;


LIBNAME OUT CLEAR ;

 

If you have 800ish table, then you may want to leverage your meta data, meaning throwing the table names into a macro variable to avoid typing.

PGStats
Opal | Level 21

Two details to add to @Haikuo's suggestion

 

1) if your dataset names have a common prefix, you can use a dataset list in the set statement: SET TABLE: ;

2) If you want to remember which dataset the rows came from, you can add a third field to the data:

 

DATA OUT.want;
length from $32;
SET table: INDSNAME=DS;
from = scan(DS, 2, ".");
RUN;
PG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 921 views
  • 2 likes
  • 3 in conversation