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 |
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;
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: