BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Q1983
Lapis Lazuli | Level 10

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

3 REPLIES 3
DanZ
Obsidian | Level 7

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.

Astounding
PROC Star

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 3 replies
  • 986 views
  • 7 likes
  • 4 in conversation