DATA Step, Macro, Functions and more

Multiple datasets into one

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 143
Accepted Solution

Multiple datasets into one

I have separate datasets located in a libname called test1

 

Region_20160201

Region_20160202

Region_20160203

Region_20160204

Region_20160205

The numbers represent dates

 

example datastep:

data test1.Region_20160201;

run;

Here is some sample data for each Region

ID         File_Date           Data_Type

11           11/15/05              R

12           05/05/11              T

 

I want to create a single data-set that combines all of the individual datasets based on the previous month. Therefore we would have this

Region_201602 and then have all 5 datasets combined into one dataset.


Accepted Solutions
Solution
‎04-01-2016 09:25 AM
Super User
Posts: 5,518

Re: Multiple datasets into one

If you inspect carefully the names of all the data sets, you may be able to use a shorter program that uses:

 

set test1.Region_201602: ;

 

The colon is a shortcut that refers to all data set names beginning with the prior characters. 

 

Don't run this program twice.  If you actually create the combined data set using the name Region_201602, that name would be included in the list the next time you use the colon.

View solution in original post


All Replies
Contributor
Posts: 38

Re: Multiple datasets into one

If you're just appending them then this will do:

 

data want;
set
test1.Region_20160201
test1.Region_20160202
test1.Region_20160203
test1.Region_20160204
test1.Region_20160205
;
run;

But, are you wanting to pull the date of the file into the output data? I couldn't tell from your question.

Solution
‎04-01-2016 09:25 AM
Super User
Posts: 5,518

Re: Multiple datasets into one

If you inspect carefully the names of all the data sets, you may be able to use a shorter program that uses:

 

set test1.Region_201602: ;

 

The colon is a shortcut that refers to all data set names beginning with the prior characters. 

 

Don't run this program twice.  If you actually create the combined data set using the name Region_201602, that name would be included in the list the next time you use the colon.

Super User
Super User
Posts: 7,997

Re: Multiple datasets into one

Hi,

 

@Astounding has given you a good idea there.  I would just add that having "data" in table names - or column names - which are for programming purposes is not a good idea.  All you are doing is making your programming life harder.  If you need separate files then use a consitent prefix and incrementor, for example ds1, ds2 etc.  However in most cases, unless your data is huge then having one dataset with a varibale to identify dataset ould be far easier to work with:

data total;
  set test1.region_2016: indsname=tmp;
  ds_name=input(tranwrd(tmp,"Region_",""),yymmdd8.);
format ds_name date9.; run;

The above will load all the datasets into one dataset called total, and in there you will have a variable with the date from filename.  Then you can process this one dataset - no need for looping or knowing lots of file names.  You can use by group processing on it.  You can use the date data for other calculations etc.  Data should go in a dataset, column names and dataset names are there to be used for programming, column labels and dataset labels are there for people to look at.

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 295 views
  • 7 likes
  • 4 in conversation