Summation with constraints

[katja_sas_user]

Hi,

I have a dataset that looks like this:

Patient_ID	Admission_date	Comorbidity
A	14/05/2011	1
B	21/04/2011	0
C	30/06/2011	2
C	23/09/2011	3
C	22/06/2012	1
D	25/04/2011	2
D	26/04/2012	1
E	01/12/2011	1
E	04/12/2011	2
E	29/04/2012	3
E	30/04/2013	2

It's a large dataset of patients with different admission dates and different recorded

comorbidity scores. I am interested in the sum (patient level) of their scores for the period of 365 days before admission. 

If the patient was not admitted before, then the score is 0. If the previous admission is more than 365 days ago, the score is also 0. If there is more than 1 admission in the previous year, it's the sum of the scores.

In the table above, the results would look like this:

Patient_ID	Admission_date	Comorbidity	Past_Score
A	14/05/2011	1	0
B	21/04/2011	0	0
C	30/06/2011	2	0
C	23/09/2011	3	2
C	22/06/2012	1	5
D	25/04/2011	2	0
D	26/04/2011	1	2
E	01/12/2011	1	0
E	04/12/2011	2	1
E	29/04/2012	3	3
E	30/04/2013	2	0

I tried many different approaches (proc means, changing the dataset to wide,...) but 
I am struggling to find a working solution.

I am using SAS Enterprise Guide, version 7.1

Any help will be greatly appreciated!

Many thanks.

[Reeza · Accepted solution]

SQL is a good choice for this type of question. 

You join the table to itself, within period of a year. 

Untested:

Proc SQL;

create table want as

select a.*, sum(b.comorbidity) as scores

from have as a

left join have as b

on b.date between a.date-1 and a.date-365

group by a.id, a.date, a.comorbidity; 

quit; 

[Patrick]

Using @Reeza's code may be also wrap a coalesce() function around the sum() so that you get 0 and not missing for cases where there is no previous admission.

Proc SQL;
create table want as
select a.*, coalesce(sum(b.comorbidity),0) as scores
from have as a
left join have as b
on b.date between a.date-1 and a.date-365
group by a.id, a.date, a.comorbidity;
quit;

[katja_sas_user]

Thank you very much @Reeza and @Patrick. I really appreciate your help.

The code works great, the only problem arises when I keep some other variables in the table - in that case proc sql duplicates some observations.

For example:

data have;
input id date ddmmyy8. comorbidity dummy ;
format date date8.;
cards;
1 11072014 1 1
1 09022016 1 1
1 17032016 1 1
1 11012016 1 1
2 11012016 1 1
2 08022016 1 1
3 19012016 1 1
4 10012016 1 1

;
run;

proc sql;
create table want as
select a.*, coalesce(sum(b.comorbidity),0) as scores
from have as a
left join have as b
on a.id=b.id

and b.date between a.date-1 and a.date-365
group by a.id, a.date, a.comorbidity;
quit;

In this case proc sql duplicates one observation and I end up with 8 instead of original 9.

Any idea how I can solve this?

Many thanks indeed, I am really grateful!

[katja_sas_user]

Sorry, I mean I end up with 9 instead of original 8 observations.

Thank you!

Best,

K.

[Patrick]

use either a DISTINCT in the select clause or even better add the variable to the Group By clause.

[katja_sas_user]

Thanks, it's works perfectly now!