Create new rows based on existing data

[BlairWitty]

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

[japelin · Accepted solution]

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;

[BlairWitty]

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

Thank you!

[ballardw]

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.