Solved
New Contributor
Posts: 4

# Summation with constraints

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.

Accepted Solutions
Solution
‎11-26-2016 08:42 PM
Super User
Posts: 23,353

## Re: Summation with constraints

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;

All Replies
Solution
‎11-26-2016 08:42 PM
Super User
Posts: 23,353

## Re: Summation with constraints

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;

Posts: 4,697

## Re: Summation with constraints

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;

New Contributor
Posts: 4

## Re: Summation with constraints

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;

New Contributor
Posts: 4

## Re: Summation with constraints

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

Thank you!

Best,

K.

Posts: 4,697

## Re: Summation with constraints

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

New Contributor
Posts: 4

## Re: Summation with constraints

Thanks, it's works perfectly now!

☑ This topic is solved.