BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Recep
Quartz | Level 8

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

13 REPLIES 13
ballardw
Super User

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
Quartz | Level 8
Thanks a lot for letting me know! I'll create the dataset in a data step and resubmit...
Cheers,
Recep
Reeza
Super User

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
Quartz | Level 8

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
Quartz | Level 8

Hey Reeza,

 

What would be the "visit" in your code?

 

Cheers,

 

Recep

Reeza
Super User

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
Opal | Level 21

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
Super User

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
Quartz | Level 8

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
Quartz | Level 8

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
Super User

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
Opal | Level 21

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;

 

Recep
Quartz | Level 8

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!

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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