topic Re: Multiple datasets into one in SAS Programming

Multiple datasets into one

Q1983 — Thu, 31 Mar 2016 20:12:51 GMT

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.

Re: Multiple datasets into one

DanZ — Thu, 31 Mar 2016 20:18:17 GMT

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.

Re: Multiple datasets into one

Astounding — Thu, 31 Mar 2016 21:37:17 GMT

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.

Re: Multiple datasets into one

RW9 — Fri, 01 Apr 2016 08:32:32 GMT

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.