The count of number of previous admissions within the last year

[Recep]

I have a logic problem that I couldn't solve with my SAS/STAT version 9.4 TS 1M3. Below I listed some patients with IDs and hospital admission dates. I want to count the number of previous admissions within the last year. I also provided the solution by hand counting in the "number of admissions" column. The logic I would like to create in SAS would search the previous admissions of the patients and if they are within the 365 days it would sum them. If there is more than 365 days in between the admission dates by the same patients then the logic would start counting from zero again. I hope the data below and hand-counted solution are helpful to explain my question! I would appreciate if you can offer a solution!

Regards,

Recep

data admissions;
input id admission_date number_of_admissions;
datalines;
1 19088 0
1 19203 1
2 19001 0
3 19050 0
3 19070 1
3 19482 0
3 19492 1
3 19512 2
;
run;

[ballardw]

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

However if you have things in a SAS dataset then instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will create data step code you can paste in the forum as text (in a code box please opened with the {i} menu icon) or place in a TXT file and attach. Then we will have your data in a form we can test code against.

[Recep]

Thanks a lot for letting me know! I'll create the dataset in a data step and resubmit...
Cheers,
Recep

[Reeza]

Most people won't download an Excel file.

This generally is a straightforwad SQL query with a self join.Untested and you may need to muck about with the dates to get it to work. If you search 30 day re-admission problem this is the same idea, except 365 days.

proc sql;

create table want as

select a.id, a.visit, count(b.visit) as num_previous_visits

from have as a

left join have as b

on a.id=b.id and  b.date+1 < a.date < = b.date-365

group by a.id, a.visit;

quit;

[Recep]

Thank you Reeza! Even though I'm not familiar with the PROC SQL a lot I'll fiddle with the code you provided and try to figure out a solution...

Cheers,

Recep

[Recep]

Hey Reeza,

What would be the "visit" in your code?

Cheers,

Recep

[Reeza]

Some variable that uniquely identifies a visit. If you don't have one count any other variable. 

I would recommend using this on your test data first. So you can develop the code.  

[Patrick · Accepted solution]

Here the SQL @Reeza posted using your test data.

I've also added a small fix to Reeza's code logic as I believe we need to add and not substract 365 days for the date comparison as posted.

b.admission_date+1 < a.date <= b.admission_date+365

data admissions;
  input id admission_date number_of_admissions;
  format admission_date date9.;
datalines;
1 19088 0
1 19203 1
2 19001 0
3 19050 0
3 19070 1
3 19482 0
3 19492 1
3 19512 2
;
run;

proc sql;
  create table want as
  select 
    a.id, 
    a.admission_date, 
    count(b.admission_date) as num_previous_visits
  from 
    admissions as a
    left join admissions as b
      on a.id=b.id and b.admission_date+1 < a.admission_date <= b.admission_date+365
  group by a.id, a.admission_date;
quit;

[Reeza]

Yeah @Patrick I never remember if it's plus/minus for the date intervals and since I know I can test it I'm too lazy to try and remember it.

@Recep It also helps to included your desired output, but I think the solution from Patrick is correct. 

[Recep]

Thank you very much Patrick and @Reeza,

I tested the code in my dataset and it produces the desired outcome.

I really appreciate your help!

Cheers,

Recep 

[Recep]

The following solution was provided by SAS. There are a lot of ways to approach a problem for sure. It may be helpful for the ones who are not very familiar with SQL:

data one;
input id admission : mmddyy10.;
format admission mmddyy10.;
cards;
1 4/4/1952
1 7/28/1952
2 1/8/1952
3 2/26/1952
3 3/17/1952
3 5/3/1953
3 5/13/1953
3 6/2/1953
;
run;

data two;
set one;
by id admission;
prevadmiss=lag(admission);
if first.id then do;
numofadmiss=0;
prevadmiss=admission;
end;
diff=admission-prevadmiss;
if diff < 365 and not first.id then numofadmiss+1;
if diff > 365 then numofadmiss=0;
/*keep id admission numofadmiss;*/
run;

proc print;
run;

[Reeza]

That generates different solution that the SQL once your data is expanded. Please ensure the solution works for your actual data, not just your test data. But this illustrates the importance of generating test data that is reflective of your situation while still keeping it simple enough. 

Check this data set out:

 data one;
input id admission : mmddyy10.;
format admission mmddyy10.;
cards;
1 4/4/1952
1 7/28/1952
1 5/28/1953
1 5/28/1954
1 7/28/1954
1 11/28/1954
2 1/8/1952
3 2/26/1952
3 3/17/1952
3 5/3/1953
3 5/13/1953
3 6/2/1953
;
run;

data two;
set one;
by id admission;
prevadmiss=lag(admission);
if first.id then do;
numofadmiss=0;
prevadmiss=admission;
end;
diff=admission-prevadmiss;
if diff < 365 and not first.id then numofadmiss+1;
if diff > 365 then numofadmiss=0;
/*keep id admission numofadmiss;*/
run;

proc print;
run;

proc sql;
  create table want as
  select 
    a.id, 
    a.admission, 
    count(b.admission) as num_previous_visits
  from 
    one as a
    left join one as b
      on a.id=b.id and b.admission+1 < a.admission <= b.admission+365
  group by a.id, a.admission;
quit;

[Patrick]

Looks like Tech Support interpreted your requirement differently.  

I've added the following line to your data to demonstrate the difference: 3 9/17/1952

The code just adds up visits and only resets the counter if there is a gap of more than a year between two consecutive records and now that I've added a record for ID 3 there is never such a gap and you end up with a count over more than one year for the last few records.

I understood that you have been asking for a roling count of visits within a year. That's what Reeza's solution does.

data one;
  input id admission : mmddyy10.;
  format admission mmddyy10.;
  cards;
1 4/4/1952
1 7/28/1952
2 1/8/1952
3 2/26/1952
3 3/17/1952
3 9/17/1952
3 5/3/1953
3 5/13/1953
3 6/2/1953
;
run;

data two;
  set one;
  by id admission;
  prevadmiss=lag(admission);

  if first.id then
    do;
      numofadmiss=0;
      prevadmiss=admission;
    end;

  diff=admission-prevadmiss;

  if diff < 365 and not first.id then
    numofadmiss+1;

  if diff > 365 then
    numofadmiss=0;

  /*keep id admission numofadmiss;*/
run;

proc print;
run;

proc sql;
  create table want as
  select 
    a.id, 
    a.admission, 
    count(b.admission) as num_previous_visits
  from 
    one as a
    left join one as b
      on a.id=b.id and b.admission+1 < a.admission <= b.admission+365
  group by a.id, a.admission;
quit;

[Recep]

Thank you again @Patrick and @Reeza! It was a major miss in my account. Perhaps I couldn't explain my problem to the SAS help desk properly (though you got the same problem definition from me and you got it right:)). 

As Reeza said it is crucial to create a representative data set. Lesson learnt!