How to perform a calculation by comparing dates when multiple records available

[knveraraju91]

Dear,

I need to add data set     aa (variable=val)   values with dataset bb(variable=weight)  values by comparing dates. In my data, subject id=1 has multiple records in dataset   aa   and bb.   I need to derive a variable   sum    for  all    aa   records by adding  val from aa with weight  from bb. But i need to take only  bb  weight value for each   aa record only if wdtc from bb dataset is on or before dtc from aa dataset .  Thank you

Output

id  val    dtc                                        weight             sum

1 49 2018-10-16T00:00:00                 55.1               104.1

data aa;
input id val dtc $19.;
datalines;
1 49 2018-10-16T00:00:00
1 48 2018-10-17T00:00:00
1 44 2020-01-06T00:00:00
1 56 2020-03-16T00:00:00
1 43 2020-03-25T00:00:00
2 46 2020-02-06T00:00:00
2 58 2020-05-16T00:00:00
2 48 2020-07-25T00:00:00
;

data bb;
input id weight wdtc $10.;
datalines;
1 52.1 2018-10-15
1 55.1 2018-10-16
1 51.1 2018-10-17
1 50.1 2020-01-06  
1 52   2020-02-10
1 55   2020-03-25
2 58   2020-02-05
2 60   2020-02-07
2 59.2 2020-05-16
2 59   2020-07-23
2 63   2020-07-24
;

[andreas_lds]

Problem 1: You have datetimes that you store as strings.

Problem 2: The datetimes don't seem to have a relevant time-information, so maybe that can be dropped.

Problem 3: You have dates that you store as strings.

So, before i suggest something useless, please explain why you aren't storing data in the best data-type possible. And please check, why it is necessary to have time-data in a variable with 0 information-value.

[knveraraju91]

Hi

Thank you for your time.  In my previous post i posted what is in my similar actual data. I am again posting the data with numeric date values. Please suggest. Thank you

data aa;
input id val dtc ;
informat dtc yymmdd10.;
format dtc yymmdd10.;
datalines;
1 49 2018-10-16T00:00:00
1 48 2018-10-17T00:00:00
1 44 2020-01-06T00:00:00
1 56 2020-03-16T00:00:00
1 43 2020-03-25T00:00:00
2 46 2020-02-06T00:00:00
2 58 2020-05-16T00:00:00
2 48 2020-07-25T00:00:00
;

data bb;
input id weight wdtc;
informat wdtc yymmdd10.;
format wdtc yymmdd10.;
datalines;
1 52.1 2018-10-15
1 55.1 2018-10-16
1 51.1 2018-10-17
1 50.1 2020-01-06  
1 52   2020-02-10
1 55   2020-03-25
2 58   2020-02-05
2 60   2020-02-07
2 59.2 2020-05-16
2 59   2020-07-23
2 63   2020-07-24
;

[andreas_lds]

Looks as if a merge will solve the problem, but you have to sort the data and rename dtc to wdtc or vice versa:

proc sort data=aa;
   by dtc;
run;

proc sort data=bb;
   by wdtc;
run;

data cc;
   merge aa(rename=(dtc=wdtc) in=inA) bb(in=inB);
   by wdtc;

   if inA and inB;
   sum = val + weight;
run;