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?
Please post sample data and expected output.
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
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.
Thanks. But how can I change if the date value in the 'have1' datset is like ' 30Jun2015' and the merging dataset as 'test_201506'.
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;
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.
In this case, simply replace the informat date9. (in the SELECT statement) by yymmdd10..
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.