Hi Cruise, When come to date range, we usually use INTNX function. I've modified a bit of the data source, to show more observation from the output.. Hope this help. data ambulance_date;
input insurance_id amb_date;
cards;
1 16576
1 17616
1 17542
1 17814
1 17575
1 17541
1 17822
1 17703
1 18184
1 17532
1 17972
1 17935
2 16989
1 17265
2 16997
2 17008
1 18231
2 16997
2 17000
1 17056
1 16831
2 16940
2 17008
1 18162
1 18024
1 18276
1 17773
1 18299
1 18387
1 18609
1 18486
1 18486
1 18486
1 18486
1 18571
1 18488
1 18585
1 18601
1 17527
1 18722
1 18907
1 18869
1 18808
1 18760
1 18760
1 18760
1 18821
1 18903
1 18954
1 18136
2 16925
2 16974
2 16955
2 17010
1 17036
1 18455
1 18956
;
data inpatient_date;
input insurance_id inp_date;
cards;
1 16623
1 16692
1 16726
1 17712
1 16709
1 17541
1 17827
1 18110
1 17790
2 16974
2 16939
1 16952
2 16950
1 17036
2 16981
2 16974
2 16930
1 17616
1 17546
1 17240
1 17245
1 17689
2 16940
1 17895
2 17000
1 17736
1 17543
1 17672
1 18024
1 18175
1 17819
1 18184
1 18122
1 18231
1 17500
1 18299
2 16997
2 16997
2 16997
2 17008
2 16939
1 18695
1 18722
1 18722
1 18309
1 18309
1 18869
1 18836
1 18110
1 18110
1 18956
1 18321
1 18760
1 18299
1 18299
1 18299
1 18455
1 18455
1 18534
1 18857
1 18857
1 18760
1 18884
1 17988
1 17972
1 18895
;
data patient_id;
input pat_id $ insurance_id date_death date_diagnosis;
cards;
PAT009 1 18983 18884
PAT092 2 17061 16940
;
%LET RANGE = 15;
PROC SQL;
CREATE TABLE PAT_AMB_INP AS SELECT
A.pat_id,
A.insurance_id,
A.date_death AS date_death FORMAT=DATE9.,
A.date_diagnosis AS date_diagnosis FORMAT=DATE9.,
B.amb_date AS amb_date FORMAT=DATE9.,
C.inp_date AS inp_date FORMAT=DATE9.,
ABS(INTCK('DAY', date_diagnosis, amb_date)) AS AMB_RANGE,
ABS(INTCK('DAY', date_diagnosis, inp_date)) AS INP_RANGE,
CASE WHEN CALCULATED AMB_RANGE = CALCULATED INP_RANGE THEN amb_date
WHEN CALCULATED AMB_RANGE > CALCULATED INP_RANGE THEN inp_date
WHEN CALCULATED AMB_RANGE < CALCULATED INP_RANGE THEN amb_date
END AS NEAREST_DATE FORMAT DATE9.,
CASE WHEN CALCULATED AMB_RANGE = CALCULATED INP_RANGE THEN 'BOTH'
WHEN CALCULATED AMB_RANGE > CALCULATED INP_RANGE THEN 'INPATIENT'
WHEN CALCULATED AMB_RANGE < CALCULATED INP_RANGE THEN 'AMBULANCE'
END AS SOURCE FORMAT $15.
FROM patient_id AS A
LEFT JOIN ambulance_date AS B ON A.insurance_id = B.insurance_id
AND INTNX('DAY', A.date_diagnosis, -&RANGE) <= B.amb_date <= INTNX('DAY', A.date_diagnosis, &RANGE) /* predefined date range (BETWEEN date_diagnosis-15 and date_diagnosis+15) */
LEFT JOIN inpatient_date AS C ON A.insurance_id = C.insurance_id
AND INTNX('DAY', A.date_diagnosis, -&RANGE) <= C.inp_date <= INTNX('DAY', A.date_diagnosis, &RANGE) /* predefined date range (BETWEEN date_diagnosis-15 and date_diagnosis+15) */
;
QUIT;
... View more