topic Re: Summation with constraints in SAS Data Management

Summation with constraints

katja_sas_user — Sat, 26 Nov 2016 09:02:12 GMT

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.

Re: Summation with constraints

Reeza — Sat, 26 Nov 2016 11:11:57 GMT

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;

Re: Summation with constraints

Patrick — Sun, 27 Nov 2016 00:06:49 GMT

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;

Re: Summation with constraints

katja_sas_user — Sun, 27 Nov 2016 01:22:40 GMT

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!

Re: Summation with constraints

katja_sas_user — Sun, 27 Nov 2016 01:24:14 GMT

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

Thank you!

Best,

Re: Summation with constraints

Patrick — Sun, 27 Nov 2016 01:35:36 GMT

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

Re: Summation with constraints

katja_sas_user — Sun, 27 Nov 2016 01:42:22 GMT

Thanks, it's works perfectly now!