topic Comparing dates from different data points and merge based on condition in SAS Programming

Comparing dates from different data points and merge based on condition

nbora — Tue, 18 Jun 2024 13:48:38 GMT

I have a ( kind of specific) problem, I would say.
Here is the dataset I have:

data have;
    infile datalines delimiter='|' dsd truncover;
    input client $ account $ start :date9. end :date9.;
    format start end date9.;
datalines;
123 | 123ABC | 30JUN2019 | 30SEP2019
321 | 123ABC | 30JUN2020 | 31JUL2020
456 | 456ABC | 30JUN2021 | 30SEP2023
456 | 456ABC | 30JUN2018 | 30NOV2019
654 | 456ABC | 01DEC2019 | 05FEB2020
;
run;

For this data I want to compare the end date from the current datapoint with the start date of the next datapoint and calculate the date difference between these two dates. If the difference is shorter than 4 days I want to merge the two data points. So in this case the output should be:

data have;
    infile datalines delimiter='|' dsd truncover;
    input client $ account $ start :date9. end :date9.;
    format start end date9.;
datalines;
123 | 123ABC | 30JUN2019 | 30SEP2019
321 | 123ABC | 30JUN2020 | 31JUL2020
456 | 456ABC | 30JUN2021 | 30SEP2023
456 | 456ABC | 30JUN2018 | 05FEB2020
;
run;

ChatGPT did fail to produce the desired output.

Cheers,

Re: Comparing dates from different data points and merge based on condition

ballardw — Tue, 18 Jun 2024 14:07:31 GMT

You really want to combine dates across different clients????

Re: Comparing dates from different data points and merge based on condition

nbora — Tue, 18 Jun 2024 15:24:14 GMT

Yes. Even if they different clients the "belong" to the same account.

Re: Comparing dates from different data points and merge based on condition

JOL — Tue, 18 Jun 2024 19:46:03 GMT

The following code works based on the data provided. This assumes the data is sorted by client and uses by group processing and the lag function.

data have2;
set have;
by client;
p_end = lag(end);
p_start = lag(start);
p_client = lag(client);
p_account = lag(account);
diff = abs(p_end-start);
format p_start p_end date9.;

if 0<= diff <=4 then
do;
client = p_client;
account = p_account;
start = p_start;
end;

if first.client =0 then delete;
keep client account start end;
run;