Bring in a value from one dataset into another based on date match (multiple times at once)

[dmwilliams]

I have two datasets formatted as so:

Dataset1

ID        Day1               Day2            Day3                   Day4              Day5 

1          10/12/2013      10/11/2013  10/10/2013        10/09/2013     10/08/2013

2          11/09/2013      11/08/2013  11/07/2013         11/06/2013     11/05/2013

…

Dataset2Dates, Matching, Multiple Datasets

Date                Value

10/07/2013      5

10/08/2013      10

10/09/2013      3

10/10/2013      7

….

Both are very large datasets (thousands of records). I would like to match by date between the datasets (Day1 and Date, Day2 and Date, etc.) and bring the Value associated with the matched date in Dataset2 into Dataset1 for that ID (or create a new dataset with Dataset1 info with the new values for each date). I would like to do that for each ID in Dataset 1 for Day1-Day5. Ultimately, resulting in something like this for all IDs and their associated Days in order to bring the values associated with that date into the dataset with the IDs:

DatasetX?

ID        Day1               Day1Value            Day2                     Day2Value                  …

1          10/12/2013      5                            10/11/2013            3                                  …

2          11/09/2013      8                            11/08/2013            20                                ….

I've experimented with bringing the second dataset into the first and making dummy variables, but it won't work as of yet. Example: 

data work.test1;
set work.test;
format Day1 MMDDYY10. Date MMDDYY10. Day2 MMDDYY10.;
if Day1=Date then DAY1Value=Value;
if Day2=Date then DAY2Value=Value;
run;

I've experimented with proc sql as well, but only successful one date at a time (with such large data this isn't feasible), whereas I need to do this for multiple dates at once to bring multiple values over based on those dates. Example:

proc sql;
create table test3 as
select *
from Dataset1, Dataset2
where Day1=Date
;
quit;

I have yet to try do loops, but not sure how to put that together.  Any help is GREATLY appreciated. Thank you! Note, I am using SAS 9.4.

[Tom · Accepted solution]

It would be a lot easier if you just had one date per observation in the first dataset.  You could use PROC TRANSPOSE to fix that.  Then just just need to merge the two dataset by DATE.

But you might be able to just make a format from the second table

data format;
  fmtname='day_value';
  set two;
  rename date=start value=label;
run;
proc format cntlin=format ;
run;

and use it to convert.

data want;
  set one;
  array day [5];
  array value [5];
  do index=1 to dim(day);
    value[index]=input(put(day[index],day_value.),??32.);
  end;
  drop index;
run;

Result:

id        day1       day2       day3       day4       day5 value1 value2 value3 value4 value5

  1  2013-10-12 2013-10-11 2013-10-10 2013-10-09 2013-10-08    .      .      7      3     10
  2  2013-11-09 2013-11-08 2013-11-07 2013-11-06 2013-11-05    .      .      .      .      .

[dmwilliams]

That worked! THANK YOU SO MUCH!

[amitmalaviya]

I have created testing code as per your requirement. Please find the below testing code.

Data test1;
input id day1 :mmddyy10. day2 :mmddyy10. day3 :mmddyy10. day4 :mmddyy10. day5 :mmddyy10. ;
format day1 day2 day3 day4 day5 mmddyy10. ;
datalines ;
1 10/12/2013 10/11/2013 10/10/2013 10/09/2013 10/08/2013
2 11/09/2013 11/08/2013 11/07/2013 11/06/2013 11/05/2013
;
run;

proc sort data=test1;
by id;
run;

proc transpose data=test1 out=test1_t;
by id;
var day1 day2 day3 day4 day5;
run;

Data test1_t(drop=col1);
set test1_t;
date_n=col1;
day_d=_name_;
format date_n date9. ;
run;

proc sort data=test1_t;
by date_n;
run;

Data test2;
input x date :mmddyy10. value ;
format date mmddyy10. ;
datalines ;
1 10/07/2013 5
1 10/08/2013 10
1 10/09/2013 3
1 10/10/2013 7
;
run;

Data test2_(drop=x date );
set test2;
date_n=date;
format date_n date9. ;
run;

proc sort data=test2_;
by date_n;
run;

Data test_fin;
merge test1_t(in=a) test2_(in=b);
by date_n;
if a;
run;

Proc sort data=test_fin;
by id;
run;
*** Transpose the dataset and cretae dataset for date ***;
proc transpose data=test_fin out=test_fint1(drop=_name_);
by id;
var date_n;
id day_d;
run;

*** Transpose the dataset and cretae dataset for value ***;
proc transpose data=test_fin out=test_fint2(drop=_name_) suffix=val;
by id;
var value;
id day_d;
run;

*** Merge Date and value datasets ******;
Data test_all;
merge test_fint1(in=a) test_fint2(in=b);
by id;
run;