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
;
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.
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
;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.