DATA Step, Macro, Functions and more

SAS reading data

Reply
New Contributor
Posts: 4

SAS reading data

 I have 2 variables and 3 records in a sas data set, and based on the date field in that data set, I need to read different monthly data sets. For example, I have item no. Date 1 30Jun2015 2 31Jul2015 3 31Aug2015 When I read the first record, then based on the date field (30jun2015) here, it should merge another dataset suffixed with 30jun2015 with this current dataset. How can I achieve that?

Super User
Posts: 19,770

Re: SAS reading data

Please post sample data and expected output.

New Contributor
Posts: 4

Re: SAS reading data

[ Edited ]

 

Input dataset1:
item no. Date
1 30Jun2015
2 31Jul2015
3 31Aug2015
Input dataset2:(test_30june2015)
item no phone no
1 1111111111
4
5
Input dataset3:(test_31jul2015)
item no phone no
2 2222222222
7
 
Expected output:
item no. Date phone no
1 30Jun2015 1111111111
2 31Jul2015 2222222222
3 31Aug2015
Super User
Posts: 5,424

Re: SAS reading data

One way could be to use call execute in a data step that reads the control table. The call execute could call a macro that does the merging.
Data never sleeps
Trusted Advisor
Posts: 1,117

Re: SAS reading data

Alternatively, the following approach might work (depending on your real data):

 

/* Create test data */

data have1;
input item date :date9.;
format date date9.;
cards;
1 30Jun2015
2 31Jul2015
3 31Aug2015
;

data test_30jun2015; /* changed from test_30june2015! */
input item phone;
cards;
1 1111111111
4 .
5 .
;

data test_31jul2015;
input item phone;
cards;
2 2222222222
7 .
; 

/* Select datasets and merge them with HAVE1 */

proc sql noprint;
select 'test_'||put(date,date9.) as dsname into :dslist separated by ' '
from have1
where exist(calculated dsname, 'DATA');
quit;

data want;
merge have1(in=a) &dslist;
by item;
if a;
run;

 

 

With your sample data, item no. could be used as well to select observations (and the file name test_30june2015 could be left unchanged):

 

proc sql noprint;
select item into :itemlist separated by ' '
from have1;
quit;

data want;
merge have1 test_:;
by item;
where item in (&itemlist);
run;

(But this might not be applicable to your real data.)

 

 

In any case the above merge steps would create unpleasant INFO messages in the log (if option MSGLEVEL=I):

 

INFO: The variable phone on data set WORK.TEST_30JUN2015 will be overwritten by data set WORK.TEST_31JUL2015.

 

 

New Contributor
Posts: 4

Re: SAS reading data

Posted in reply to FreelanceReinhard

Thanks. But how can I change if the date value in the 'have1' datset is like ' 30Jun2015' and the merging dataset as 'test_201506'.

Trusted Advisor
Posts: 1,117

Re: SAS reading data

The following modification could work with character dates and dataset names of the form test_YYYYMM:

/* Create test data */

data have1;
input item date :$9.;
cards;
1 30Jun2015
2 31Jul2015
3 31Aug2015
;

data test_201506;
input item phone;
cards;
1 1111111111
4 .
5 .
;

data test_201507;
input item phone;
cards;
2 2222222222
7 .
; 

/* Select datasets and merge them with HAVE1 */

proc sql noprint;
select 'test_'||compress(put(input(date,date9.),yymm.),'M') as dsname into :dslist separated by ' '
from have1
where exist(calculated dsname, 'DATA');
quit;

data want;
merge have1(in=a) &dslist;
by item;
if a;
run;
New Contributor
Posts: 4

Re: SAS reading data

[ Edited ]
Posted in reply to FreelanceReinhard

Thanks. It is working now. And one last thing. how we can treat if the date field is like 2014-08-18 and we need to do the same thing.

Trusted Advisor
Posts: 1,117

Re: SAS reading data

In this case, simply replace the informat date9. (in the SELECT statement) by yymmdd10..

Super User
Posts: 10,020

Re: SAS reading data

The point is the MERGE BY variale is item and date or just item ?

 

 

data have1;
input item date :date9.;
format date date9.;
cards;
1 30Jun2015
2 31Jul2015
3 31Aug2015
;

data test_30jun2015; /* changed from test_30june2015! */
input item phone;
cards;
1 1111111111
4 .
5 .
;

data test_31jul2015;
input item phone;
cards;
2 2222222222
7 .
; 
data test(index=(item date));
 length dsname $ 40;
 set test_: indsname=dsname;
 date=input(scan(dsname,-1,'_'),date9.);
 format date date9.;
run;

data want;
 merge have1(in=ina) test;
 by item date;
 if ina;
run;
Ask a Question
Discussion stats
  • 9 replies
  • 333 views
  • 1 like
  • 5 in conversation