topic Re: Create new rows based on existing data in SAS Programming

Create new rows based on existing data

BlairWitty — Wed, 11 May 2022 21:35:55 GMT

Hello all,

I currently have a dataset in which each row represents one patient and includes data on their admission and discharge dates to the hospital. I ultimately need the dataset to include 1 row for each patient for each week they were admitted. For example, if a patient was in hospital for weeks 1-4 of 2020 they would have a row for week 1, week 2, week 3, and week 4.

I have been getting nowhere close with code attempts. But know that if Week of Admission is not equal to Week of Discharge I need the number of new rows generated to be the difference between the two. Any help is greatly appreciated!

Current Data Structure

Patient ID	Week of Admission	Week of Discharge
1	1	1
2	2	4
3	1	3
4	6	8

Desired Data Structure (New columns and rows highlighted in green text)

Patient ID	Hospital week	Week of Admission	Week of Discharge
1	1	1	1
2	2	2	4
2	3
2	4
3	1	1	3
3	2
3	3
4	6	6	8
4	7
4	8

Re: Create new rows based on existing data

japelin — Thu, 12 May 2022 00:18:52 GMT

Which results do you want?

Are you sure you want the results of Want2?

data have;
  input Patient_ID	Week_of_Admission	Week_of_Discharge;
datalines;
1	1	1
2	2	4
3	1	3
4	6	8
;
run;

data want;
  set have;
  do Hospital_week=Week_of_Admission to Week_of_Discharge;
    output;
  end;
run;

data want2;
  set have;
  si=Week_of_Admission;
  ei=Week_of_Discharge;
  Hospital_week=Week_of_Admission;
  output;

  do Hospital_week=si+1 to ei;
    Week_of_Admission=.;
    Week_of_Discharge=.;
    output;
  end;
  drop si ei;
run;

Re: Create new rows based on existing data

ballardw — Thu, 12 May 2022 13:37:46 GMT

Before using "week" for anything you may want to provide what your definition of "week" is. Does it have a fixed day of the week to start? If so which day of the week is it? Or is are you wanting 7-day intervals starting on the first date encountered?

Also, you don't show any actual dates. Working with dates is likely to make this much more approachable if you want to split anything up.

Re: Create new rows based on existing data

BlairWitty — Thu, 12 May 2022 14:30:39 GMT

Either option is great, thank you so much! I was clearly overthinking this and making it much more complicated!

Thank you!