topic Re: insert rows that are duplicated using a BY variable in SAS Programming

insert rows that are duplicated using a BY variable

stefaniloran — Thu, 18 Apr 2019 18:45:19 GMT

I have used a data step to create a timeline of patient events. I now want to go back and create a table that records the first 5 events after every inpatient discharge. This will intentionally duplicate rows:

HAVE

patient_id facility_type facility_id timeline_id position_id

1 INP A 1 1

1 SNF A 2 2

1 HHA A 3 3

1 INP B 4 1

1 HHA B 5 2

1 HOS B 6 3

2 INP A 1 1

2 HHA A 2 2

2 HOS A 3 3

2 INP B 4 1

2 HOM B 5 2

2 HOS B 6 3

WANT

patient_id facility_type facility_id timeline_id position_id

1 INP A 1 1

1 SNF A 2 2

1 HHA A 3 3

1 INP A 4 4 /*new row*/

1 HHA A 5 5 /*new row*/

1 HOS A 6 6 /*new row*/

1 INP B 4 1

1 HHA B 5 2

1 HOS B 6 3

2 INP A 1 1

2 HHA A 2 2

2 HOS A 3 3

2 INP A 4 4 /*new row*/

2 HOM A 5 5 /*new row*/

2 HOS A 6 6 /*new row*/

2 INP B 4 1

2 HOM B 5 2

2 HOS B 6 3

Using version 7.15

Re: insert rows that are duplicated using a BY variable

ballardw — Thu, 18 Apr 2019 19:04:18 GMT

@stefaniloran wrote:

I have used a data step to create a timeline of patient events. I now want to go back and create a table that records the first 5 events after every inpatient discharge. This will intentionally duplicate rows:

You should provide a rule or values that tell us when an inpatient discharge occurs. It appears that your rule might actually be "insert before value of INP for the variable Facility_type. If not, plus expand as "after" is occurring after different values. And what are the rules for the inserted Facility_type? Your example shows two different sets of inserted facility_type but no description where the values come from.

Re: insert rows that are duplicated using a BY variable

stefaniloran — Thu, 18 Apr 2019 19:37:05 GMT

Inpatient events are determined by admit/discharge dates. Assume that the
example is already sorted by the admit date descending.

One idea I just had (thanks to your reply) is to start by defining all the
INP discharge events in a separate table and then join to the full timeline
to pick out the top 5 events for that patient_id that occur after the INP
event...

Re: insert rows that are duplicated using a BY variable

ballardw — Thu, 18 Apr 2019 22:17:49 GMT

@stefaniloran wrote:
Inpatient events are determined by admit/discharge dates. Assume that the
example is already sorted by the admit date descending.

One idea I just had (thanks to your reply) is to start by defining all the
INP discharge events in a separate table and then join to the full timeline
to pick out the top 5 events for that patient_id that occur after the INP
event...

And you don't include the dates or and indicator for admit/discharge. So how do we know where to insert based on the values in the data provided?