Proc sql conditional date values merge

[raja777pharma]

Hi,

I have two dataset and need to be assign value based on date ranges .

subject	Siteid	Test	date
1001	201	LDH	12-Aug-24
1001	201	LDH	22-Aug-24
1001	201	LDH	31-Sep-24
1001	201	ALT	12-Aug-24
1001	201	ALT	12-Aug-24
1002	202	LDH	18-Mar-24
1002	202	LDH	18-Mar-24
1002	202	ALT	18-Mar-24
1002	202	ALT	18-Mar-24
1002	202	ALT	25-Jun-24

Below Value need to add above dataset where same siteid and test and the date value between stdt and endt.

if endt is missing then use only date and stdt 

for example subject 1001 and siteid = 201 ,test = LDH , date = 31Sep24 , value need be assign 27 and rest of two records will get value 24 .

siteid	test	stdt	endt	value
201	LDH	10-Aug-24	15-Aug-24	24
201	LDH	30-Sep-24		27
201	ALT	10-Aug-24	15-Aug-24	1002
202	LDH	15-Mar-24	28-Mar-24	35
202	ALT	15-Mar-24	28-Mar-24	1021
202	ALT	20-Jun-24		1031

[himself]

/* Kindly Note that September has dates upto 30th*/
data dataset1;
    input subject Siteid Test $ date :date9.;
    format date date9.;
    datalines;
1001 201 LDH 12-Aug-24
1001 201 LDH 22-Aug-24
1001 201 LDH 31-Sep-24
1001 201 ALT 12-Aug-24
1001 201 ALT 12-Aug-24
1002 202 LDH 18-Mar-24
1002 202 LDH 18-Mar-24
1002 202 ALT 18-Mar-24
1002 202 ALT 18-Mar-24
1002 202 ALT 25-Jun-24
;
run;

data dataset2;
    input Siteid Test $ stdt :date9. endt :date9. value;
    format stdt endt date9.;
    datalines;
201 LDH 10-Aug-24 15-Aug-24 24
201 LDH 30-Sep-24 . 27
201 ALT 10-Aug-24 15-Aug-24 1002
202 LDH 15-Mar-24 28-Mar-24 35
202 ALT 15-Mar-24 28-Mar-24 1021
202 ALT 20-Jun-24 . 1031
;
run;

proc sql;
    create table merged as
    select a.*, b.value
    from dataset1 as a
    left join dataset2 as b
    on a.Siteid = b.Siteid and a.Test = b.Test
    and (a.date between b.stdt and coalesce(b.endt, a.date));
quit;

proc print data=merged;
run;

The date “31-Sep-24” is missing because September only has 30 days. Therefore, “31-Sep-24” is not a valid date.

To correct this, you should use a valid date within September, such as “30-Sep-24”. Here’s the updated dataset with the corrected date:

data dataset1;
    input subject Siteid Test $ date :date9.;
    format date date9.;
    datalines;
1001 201 LDH 12-Aug-24
1001 201 LDH 22-Aug-24
1001 201 LDH 30-Sep-24
1001 201 ALT 12-Aug-24
1001 201 ALT 12-Aug-24
1002 202 LDH 18-Mar-24
1002 202 LDH 18-Mar-24
1002 202 ALT 18-Mar-24
1002 202 ALT 18-Mar-24
1002 202 ALT 25-Jun-24
;
run;

data dataset2;
    input Siteid Test $ stdt :date9. endt :date9. value;
    format stdt endt date9.;
    datalines;
201 LDH 10-Aug-24 15-Aug-24 24
201 LDH 30-Sep-24 . 27
201 ALT 10-Aug-24 15-Aug-24 1002
202 LDH 15-Mar-24 28-Mar-24 35
202 ALT 15-Mar-24 28-Mar-24 1021
202 ALT 20-Jun-24 . 1031
;
run;

proc sql;
    create table merged as
    select a.*, b.value
    from dataset1 as a
    left join dataset2 as b
    on a.Siteid = b.Siteid and a.Test = b.Test
    and (a.date between b.stdt and coalesce(b.endt, a.date));
quit;

proc print data=merged;
run;