topic Re: How to perform a calculation by comparing dates when multiple records available in SAS Programming

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

knveraraju91 — Tue, 19 Jan 2021 06:17:50 GMT

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
;

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

andreas_lds — Tue, 19 Jan 2021 06:43:11 GMT

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.

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

knveraraju91 — Tue, 19 Jan 2021 15:36:21 GMT

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
;

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

andreas_lds — Wed, 20 Jan 2021 07:56:42 GMT

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;