Hello who can help me with a loop function that runs through the days between a start date and an end date.
I am a beginner(newbee) in SAS proc SQL so please provide clear instructions
I work with a hospital admission table in which per patient a start date of the admission and the end date of the admission is stated.
Now I am looking for a loop function that runs through the day of the hospital admission.
The output is then used for further calculations and linking to other tables such as pain measurements per day.
Hopefully my question is clear and someone can help me. SAS DI version 4.903
Hello Kurt
Your solution works like a train. Had to make a small adjustment in case the clinic start_admission_date and the clinic
end _admission_date fall on the same day. Patient stays in the hospital for only a few hours.
Maybe you have a smarter solution than my adjustment. Thank you for the suggested solution.
data want;
set have (rename=(end_date_admission=end_date));
if end_date = . then end_date = datetime();
day_date = datepart(start_date_admission);
if datepart(d_opn) ne datepart(end_date) then
do until (day_date >= datepart(end_date));
end_date_admission = intnx('dtday',start_date_admission,1,'b');
duration = intck('minute',start_date_admission,end_date_admission);
output;
day_date = day_date + 1;
start_date_admission = end_date_admission;
end;
;
end_date_admission = end_date;
duration = intck('minute',start_date_admission,end_date_admission);
output;
drop end_date;
run;
A simple do loop:
data want;
set have (rename=(end_date_admission=end_date));
if end_date = . then end_date = datetime();
day_date = datepart(start_date_admission);
do until (day_date >= datepart(end_date));
end_date_admission = intnx('dtday',start_date_admission,1,'b');
duration = intck('minute',,start_date_admission,end_date_admission);
output;
day_date = day_date + 1;
start_date_admission = end_date_admission;
end;
end_date_admission = end_date;
duration = intck('minute',start_date_admission,end_date_admission);
output;
drop end_date;
run;
Untested, for lack of usable data.
Please post example data in a data step with datalines, so we can easily recreate your dataset(s) with a copy/paste and submit. Nobody here is keen on tediously typing data off a picture. Help us to help you.
Hello Kurt
Your solution works like a train. Had to make a small adjustment in case the clinic start_admission_date and the clinic
end _admission_date fall on the same day. Patient stays in the hospital for only a few hours.
Maybe you have a smarter solution than my adjustment. Thank you for the suggested solution.
data want;
set have (rename=(end_date_admission=end_date));
if end_date = . then end_date = datetime();
day_date = datepart(start_date_admission);
if datepart(d_opn) ne datepart(end_date) then
do until (day_date >= datepart(end_date));
end_date_admission = intnx('dtday',start_date_admission,1,'b');
duration = intck('minute',start_date_admission,end_date_admission);
output;
day_date = day_date + 1;
start_date_admission = end_date_admission;
end;
;
end_date_admission = end_date;
duration = intck('minute',start_date_admission,end_date_admission);
output;
drop end_date;
run;
Given your variable names, the condition should be
if datepart(start_date_admission) ne datepart(end_date) then
Otherwise, it solves the problem of one-day stays very nicely.
Hello Kurt,
Your proc sql procedure works well.
But I have an additional question.
The output of the procedure is written to an Oracle table, but now Oracle does not correctly process the format of the "day_date" field.
Is it possible to make the field "day_date" a datetime20. format?
Thanks in advance for your response.
To convert a date to a datetime value, do
day_date = dhms(day_date,0,0,0);
format day_date datetime20.;
or in SQL
dhms(day_date,0,0,0) format=datetime20. as day_date
Hello Kurt,
May I again appeal to you for your SAS knowledge.
In nursing, we use three daily rosters for the nurses.
Now we would like to split the length of stay on the ward per calendar day into the duration of stay per roster.
This is quite a puzzle since the length of stay can start in roster1 and end in roster2 or start in roster1 and also end in roster1 etc. etc..
Maybe you have a suggestion, idea or solution.
Thanks in advance for your response.
Roster1 23:30 until 07:30 (>23:30 <=07:30) (duration 8 hours --> 30 minutes until midnight and 450 minutes until start roster2)
Roster2 07:30 until 15:30 (>07:30 <=15:30) (duration 8 hours)
Roster3 15:30 until 23:30 (>15:30 <=23:30) (duration 8 hours)
data_input
desired output
Standard SQL does not have a Do loop construct.
My take on this would be joining with a date/time dimension using a BETWEEN AND join criteria.
Thus avoiding using User Written code.
Hello Linus, As a learning newbee, I also looked at your proposed solution.
Unfortunately it is unclear to me how a join can make the intervening records visible between the start_admission_date and the end_admission_date. I have a dim_date table. Maybe you can explain the join statement.
INPUT FROM Oracle TABLE "ADMISSION"
PATIENT_ID | ADMISSION_ID | START_DATE_ADMISSION | END_DATE_ADMISSION |
1995578 | 23692640 | 15-07-2019 00:01 | 18-07-2019 11:08 |
1378763 | 23692642 | 15-07-2019 00:25 | 15-07-2019 10:15 |
1438468 | 23692661 | 15-07-2019 02:48 | . |
1655650 | 23692662 | 15-07-2019 05:43 | . |
2473537 | 23693801 | 15-07-2019 13:40 | 18-07-2019 14:57 |
1433512 | 23694003 | 15-07-2019 14:23 | 22-07-2019 13:15 |
Records for July 2019 in the dim_date table.
SK_DIM_DATE | DATE9 | DDMMYYD10 | DATE_CHAR |
43646 | 01JUL2019:00:00:00 | 01-07-2019 | 01-jul-19 |
43647 | 02JUL2019:00:00:00 | 02-07-2019 | 02-jul-19 |
43648 | 03JUL2019:00:00:00 | 03-07-2019 | 03-jul-19 |
43649 | 04JUL2019:00:00:00 | 04-07-2019 | 04-jul-19 |
43650 | 05JUL2019:00:00:00 | 05-07-2019 | 05-jul-19 |
43651 | 06JUL2019:00:00:00 | 06-07-2019 | 06-jul-19 |
43652 | 07JUL2019:00:00:00 | 07-07-2019 | 07-jul-19 |
43653 | 08JUL2019:00:00:00 | 08-07-2019 | 08-jul-19 |
43654 | 09JUL2019:00:00:00 | 09-07-2019 | 09-jul-19 |
43655 | 10JUL2019:00:00:00 | 10-07-2019 | 10-jul-19 |
43656 | 11JUL2019:00:00:00 | 11-07-2019 | 11-jul-19 |
43657 | 12JUL2019:00:00:00 | 12-07-2019 | 12-jul-19 |
43658 | 13JUL2019:00:00:00 | 13-07-2019 | 13-jul-19 |
43659 | 14JUL2019:00:00:00 | 14-07-2019 | 14-jul-19 |
43660 | 15JUL2019:00:00:00 | 15-07-2019 | 15-jul-19 |
43661 | 16JUL2019:00:00:00 | 16-07-2019 | 16-jul-19 |
43662 | 17JUL2019:00:00:00 | 17-07-2019 | 17-jul-19 |
43663 | 18JUL2019:00:00:00 | 18-07-2019 | 18-jul-19 |
43664 | 19JUL2019:00:00:00 | 19-07-2019 | 19-jul-19 |
43665 | 20JUL2019:00:00:00 | 20-07-2019 | 20-jul-19 |
43666 | 21JUL2019:00:00:00 | 21-07-2019 | 21-jul-19 |
43667 | 22JUL2019:00:00:00 | 22-07-2019 | 22-jul-19 |
43668 | 23JUL2019:00:00:00 | 23-07-2019 | 23-jul-19 |
43669 | 24JUL2019:00:00:00 | 24-07-2019 | 24-jul-19 |
43670 | 25JUL2019:00:00:00 | 25-07-2019 | 25-jul-19 |
43671 | 26JUL2019:00:00:00 | 26-07-2019 | 26-jul-19 |
43672 | 27JUL2019:00:00:00 | 27-07-2019 | 27-jul-19 |
43673 | 28JUL2019:00:00:00 | 28-07-2019 | 28-jul-19 |
43674 | 29JUL2019:00:00:00 | 29-07-2019 | 29-jul-19 |
43675 | 30JUL2019:00:00:00 | 30-07-2019 | 30-jul-19 |
43676 | 31JUL2019:00:00:00 | 31-07-2019 | 31-jul-19 |
proc sql;
create table want as
select
a.patient_id,
a.admission_id,
b.date9
from admission a, dim_date b
where b.date9 between a.start_date_admission and a.end_date_admission
;
quit;
This is the code you need to achieve with the point-and-click interface, if you don't want to use a user written transformation in DI Studio.
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 25. 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.