DATA Step, Macro, Functions and more

The count of number of previous admissions within the last year

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

The count of number of previous admissions within the last year

[ Edited ]

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;


Accepted Solutions
Solution
‎01-10-2017 01:10 PM
Respected Advisor
Posts: 4,173

Re: The count of number of previous admissions within the last year

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;

View solution in original post


All Replies
Super User
Posts: 11,343

Re: The count of number of previous admissions within the last year

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.

Contributor
Posts: 31

Re: The count of number of previous admissions within the last year

Thanks a lot for letting me know! I'll create the dataset in a data step and resubmit...
Cheers,
Recep
Super User
Posts: 19,815

Re: The count of number of previous admissions within the last year

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;

 

Contributor
Posts: 31

Re: The count of number of previous admissions within the last year

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

Contributor
Posts: 31

Re: The count of number of previous admissions within the last year

Hey Reeza,

 

What would be the "visit" in your code?

 

Cheers,

 

Recep

Super User
Posts: 19,815

Re: The count of number of previous admissions within the last year

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.  

Solution
‎01-10-2017 01:10 PM
Respected Advisor
Posts: 4,173

Re: The count of number of previous admissions within the last year

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;
Super User
Posts: 19,815

Re: The count of number of previous admissions within the last year

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. 

 

 

Contributor
Posts: 31

Re: The count of number of previous admissions within the last year

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 

Contributor
Posts: 31

Re: The count of number of previous admissions within the last year

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;

Super User
Posts: 19,815

Re: The count of number of previous admissions within the last year

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;
Respected Advisor
Posts: 4,173

Re: The count of number of previous admissions within the last year

[ Edited ]

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.

 

Capture.PNG

 

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;

 

Contributor
Posts: 31

Re: The count of number of previous admissions within the last year

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 rightSmiley Happy). 

 

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

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 265 views
  • 2 likes
  • 4 in conversation