Counting and limiting dates

[GregorClegane]

So I have been thinking about this issue for a little bit and I am scratching my head at the best way to approach this issue.  I have 3 fields, I have Member, Discharge_DT and Service_DT.

I am trying to determine which service dates fall between 1 - 7 days between the Discharge_DT and Service_DT also

I am trying to determine which service dates fall between 1 - 30 days between the Discharge_DT and Service_DT.

I was thinking something along the lines of 

PROC SQL;

create table work.test as

select distinct

MEMBER,

DISCHARGE_DT,

(SERVICE_DT - DISCHARGE_DT) as days



from work.table

group by 1,2;

quit;

But I am not 100% on the syntax and I want to make sure I am grabbing everything based on the Discharge and not omitting anything. 

Thank you for your help

[JeffMaggio]

How are the dates formatted?

If they are not dates, you'll have to use a conversion first.

Also, I don't know your data, but are you sure you don't want 

<span class="token punctuation">(DISCHARGE_DT - </span>SERVICE_DT<span class="token punctuation">)</span> as days

 instead? Is this some kind of length of stay for health data?

If you simply want to check / filter results. You could use something like:

where DISCHARGE_DT <= SERVICE_DT + 7

You don't have any summary columns in your example, so you would not want to include a group by clause.

[GregorClegane]

@JeffMaggio  Thank you, I am playing around with your code now to see what it looks like.  The dates are just in a date9. type format.  I was also reading up on the SAS Lag function, but I still haven't gotten my mind around that.

Thanks for the start, I will respond with good/bad news!

[GregorClegane]

@JeffMaggio I have been trying your logic you suggested and I still can't seem to wrap my head around how to make it work.  Here is a sample of my data, along with what I am expecting, I hope this clarifies some questions.

Member_ID	Discharge_Date	Diagnosis	Service_Date	Diagnosis
0001	05JAN2019	428	08JAN2019	428
0001	28FEB2019	123	04MAR2019	922
0001	11JUN2019	410
0002	02AUG2019	238	29AUG2019	238
0002	01SEP2019	210
0002	04NOV2019	308	12DEC2019	242
0003	20DEC2019	105	22DEC2019	105
0004	01MAR2019	124	04APR2019	124
0005	30APR2019	185	09MAY2019	252

So in this example I am looking to count the number of days between the discharge date and new service date.  However the following needs to match.  The member id needs to match along with the diagnosis code.  So I would want to end up with something like the following:

Member_ID	Discharge_Date	Diagnosis	Service_Date	Diagnosis	Day Count
0001	05JAN2019	428	08JAN2019	428	3
0002	02AUG2019	238	29AUG2019	238	27
0003	20DEC2019	105	22DEC2019	105	2
0004	01MAR2019	124	04APR2019	124	34

Once I have that Day Count field I can start to build my flags.  

Member_ID	Discharge_Date	Diagnosis	Service_Date	Diagnosis	Day Count	Flag
0001	05JAN2019	428	08JAN2019	428	3	1-7
0001	05JAN2019	428	08JAN2019	428	3	1-30
0002	02AUG2019	238	29AUG2019	238	27	1-7
0002	02AUG2019	238	29AUG2019	238	27	1-30
0003	20DEC2019	105	22DEC2019	105	2	1-7
0003	20DEC2019	105	22DEC2019	105	2	1-30
0004	01MAR2019	124	04APR2019	124	34	na

So with each flag, it will duplicate the data since the members should fall into each bucket.

Thank you again!

[JeffMaggio]

Try something like this:

data have;
Member_ID='0001';	Discharge_Date='05JAN2019'd;	Diagnosis=428;	Service_Date='08JAN2019'd;	Diagnosis2=428; output;
Member_ID='0001';	Discharge_Date='28FEB2019'd;	Diagnosis=123;	Service_Date='04MAR2019'd;	Diagnosis2=922; output;
Member_ID='0001';	Discharge_Date='11JUN2019'd;	Diagnosis=410;	Service_Date=.;             Diagnosis2=.;	 output;
Member_ID='0002';	Discharge_Date='02AUG2019'd;	Diagnosis=238;	Service_Date='29AUG2019'd;	Diagnosis2=238; output;
Member_ID='0002';	Discharge_Date='01SEP2019'd;	Diagnosis=210;	Service_Date=.;             Diagnosis2=.;	 output;
Member_ID='0002';	Discharge_Date='04NOV2019'd;	Diagnosis=308;	Service_Date='12DEC2019'd;	Diagnosis2=242; output;
Member_ID='0003';	Discharge_Date='20DEC2019'd;	Diagnosis=105;	Service_Date='22DEC2019'd;	Diagnosis2=105; output;
Member_ID='0004';	Discharge_Date='01MAR2019'd;	Diagnosis=124;	Service_Date='04APR2019'd;	Diagnosis2=124; output;
Member_ID='0005';	Discharge_Date='30APR2019'd;	Diagnosis=185;	Service_Date='09MAY2019'd;	Diagnosis2=252; output;
format Discharge_Date date9. Service_Date date9.; 
run;

data lookup;
length Flag $8.;
Fstart = 1; Fend=7; Flag='1-7'; output;
Fstart = 1; Fend=30; Flag='1-30'; output;
run;

proc sql;
create table want as 
select
t1.*,
(t1.Service_Date - t1.Discharge_Date) as Day_Count,
case when (t1.Service_Date - t1.Discharge_Date) <= 30 then t2.Flag else 'na' end as Flag
from have t1
    left join lookup t2 on ( Fstart <= (t1.Service_Date - t1.Discharge_Date) <= Fend)
where Service_Date ne . 
having Diagnosis = Diagnosis2 and Diagnosis2 ne .
;
quit;