Tried lag and transpose. Having trouble getting what I want using either of these. Help!

[Michelle1000]

Hi Experts,

My data looks like this:                                                        WANT:            WANT:                                  WANT;

Identifier              admitday             dischargeday           COUNT           NEWADMITDAY                NEWDISDAY

1                             16JUL2023         18JUL2023                         1             16JUL2023                         18JUL2023

2                             11FEB2022         16FEB2022                        1             11FEB2022                         16FEB2022

2                             27MAR2023      03APR2023                      2             27MAR2023                     03APR2023

3                             01OCT2017        31OCT2017                        1             01OCT2017                        24JAN2018

3                             01NOV2017       30NOV2017                       1             01OCT2017                        24JAN2018

3                             01DEC2017        31DEC2017                        1             01OCT2017                        24JAN2018

3                             01JAN2018         11JAN2018                         1             01OCT2017                        24JAN2018

3                             12JAN2018         23JAN2018                         1             01OCT2017                        24JAN2018

3                             13JAN2018         24JAN2018                         1             01OCT2017                        24JAN2018

3                             23JUN2018        28JUN2018                        2             23JUN2018                        28JUN2018

4                             05FEB2021         06FEB2021                         1             05FEB2021                         14FEB2021

4                             05FEB2021         07FEB2021                         1             05FEB2021                         14FEB2021

4                             05FEB2021         11FEB2021                         1             05FEB2021                         14FEB2021

4                             05FEB2021         14FEB2021                         1             05FEB2021                         14FEB2021

I need to count hospitalizations per patient.  However, multiple overlapping hospital admissions and discharge records should be collapsed into 1 inpatient encounter.  In addition, an inpatient encounter with an admission date that occurred within 1 day of a prior inpatient encounter should be counted as a single inpatient encounter.  You can see that for identifier #3, this patient had one hospitalization from Oct 1, 2017 – Jan24 and one from June 23, 2018 – June 28, 2018.

Any help would be greatly appreciated!

[ballardw · Accepted solution]

Unless you need those "new" dates for something else this should suffice.

Please note the data step as way to provide example data. That way we have something to test code with.

If you don't provide such then we end up guessing about things like variable types and formats that may sometimes affect how the programs need to be written.

The following assumes that your data set, which I call Have, is sorted by Identifier and Admitday.

data have;
   input Identifier  admitday :date9. dischargeday :date9.;
   format admitday dischargeday date9.;
datalines;
1   16JUL2023 18JUL2023 
2   11FEB2022 16FEB2022 
2   27MAR2023  03APR2023
3   01OCT2017  31OCT2017
3   01NOV2017 30NOV2017 
3   01DEC2017  31DEC2017
3   01JAN2018 11JAN2018 
3   12JAN2018 23JAN2018 
3   13JAN2018 24JAN2018 
3   23JUN2018  28JUN2018
4   05FEB2021 06FEB2021 
4   05FEB2021 07FEB2021 
4   05FEB2021 11FEB2021 
4   05FEB2021 14FEB2021 
;

data want;
   set have;
   by identifier;
   retain count;
   ldischarge = lag(dischargeday);
   if first.identifier then count=1;
   else if admitday -ldischarge > 1 then count+1;
   drop ldischarge;
run;

The Retain means that the variable values are kept across boundaries of the data step so is easy to  use as an accumulator.

Lag will get the value from the previous observation when used unconditionally. However if you place it in an IF or similar conditional clause then lag returns the last time the condition was true. So where you place it in code is quite important for many uses.

When you use BY then SAS adds temporary variables that indicate whether the current observation is the First or Last of that value in the By group. The variables are accessed using First.<var> and Last.<var>, the dot is critical. The values are 1/0 for True or False and can be used to reset values such as when the identifier changes.

Date values are numeric, integers, so you can do arithmetic or use fancier functions like Intck or Intnx to deal with intervals. If the current admitday - previous discharge is greater than 1 that meets your condition for a "new" admission.

[Michelle1000]

Thank you so much!  This is great, however, I do need the new dates for these inpatient hospitalizations because next I need to get outpatient encounters - these are the instructions:  Outpatient encounters occurring within an inpatient (admission to discharge date) were not counted as unique outpatient encounters.

Also, the code works for ID #3, but not for ID #4:

                                                                                                       WANT:          WANT:                                  WANT:

4                             05FEB2021         06FEB2021                         1             05FEB2021                         14FEB2021

4                             05FEB2021         07FEB2021                         1             05FEB2021                         14FEB2021

4                             05FEB2021         11FEB2021                         1             05FEB2021                         14FEB2021

4                             05FEB2021         14FEB2021                         1             05FEB2021                         14FEB2021

[ballardw]

You are going to have to provide details of how Identifier = 4 didn't work. as the result I get matches your want.

Didn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the "<>" to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the "<>" icon or attached as text to show exactly what you have and that we can test code against.

And where are the "outpatient encounters"? Example data and expected results.

Since it appears to involve two data sets, this and another then the approach is how they are combined.

[Michelle1000]

So sorry!  You were right.  The count worked great.  I now need to get the outpatient data (which I haven't finished doing yet) and then will need to do as I said:  Outpatient encounters occurring within an inpatient (admission to discharge date) were not counted as unique outpatient encounters.  So, I'll get the outpatient data, try to do it, and if not, post back here!  Thank you so much for your help.