- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Please post sample data and expected output.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks. But how can I change if the date value in the 'have1' datset is like ' 30Jun2015' and the merging dataset as 'test_201506'.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
In this case, simply replace the informat date9. (in the SELECT statement) by yymmdd10..
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;