BookmarkSubscribeRSS Feed
Test_yes
Calcite | Level 5

 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?

9 REPLIES 9
Reeza
Super User

Please post sample data and expected output.

Test_yes
Calcite | Level 5

 

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
LinusH
Tourmaline | Level 20
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
FreelanceReinh
Jade | Level 19

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.

 

 

Test_yes
Calcite | Level 5

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

FreelanceReinh
Jade | Level 19

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;
Test_yes
Calcite | Level 5

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.

FreelanceReinh
Jade | Level 19

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

Ksharp
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 9 replies
  • 1189 views
  • 1 like
  • 5 in conversation