topic Re: Proc SQL Loop for days between start_date and end_date in SAS Programming

Proc SQL Loop for days between start_date and end_date

Cugel — Thu, 25 Jul 2019 12:32:11 GMT

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

Re: Proc SQL Loop for days between start_date and end_date

Kurt_Bremser — Thu, 25 Jul 2019 13:40:13 GMT

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.

Re: Proc SQL Loop for days between start_date and end_date

ballardw — Thu, 25 Jul 2019 14:57:19 GMT

Standard SQL does not have a Do loop construct.

Re: Proc SQL Loop for days between start_date and end_date

LinusH — Thu, 25 Jul 2019 20:37:03 GMT

My take on this would be joining with a date/time dimension using a BETWEEN AND join criteria.

Thus avoiding using User Written code.

Re: Proc SQL Loop for days between start_date and end_date

Cugel — Sat, 27 Jul 2019 21:06:47 GMT

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;

Re: Proc SQL Loop for days between start_date and end_date

Cugel — Sat, 27 Jul 2019 21:44:19 GMT

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

Re: Proc SQL Loop for days between start_date and end_date

Kurt_Bremser — Tue, 30 Jul 2019 06:58:48 GMT

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.

Re: Proc SQL Loop for days between start_date and end_date

Kurt_Bremser — Thu, 01 Aug 2019 06:38:22 GMT

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.

Re: Proc SQL Loop for days between start_date and end_date

Cugel — Thu, 01 Aug 2019 07:11:43 GMT

Hello Kurt, thank you very much for your input.

However, the final solution is not yet clear.

I now have insight into the days within the hospitalization period.

But the next problem is that a patient may have been in multiple nursing units on one calendar day.

I will compile a dataset that makes that clear.

To be continued.

Re: Proc SQL Loop for days between start_date and end_date

Cugel — Thu, 03 Oct 2019 11:26:11 GMT

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.

Re: Proc SQL Loop for days between start_date and end_date

Kurt_Bremser — Thu, 03 Oct 2019 11:33:47 GMT

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

Re: Proc SQL Loop for days between start_date and end_date

Cugel — Thu, 03 Oct 2019 12:04:32 GMT

Hello Kert,
Thanks the suggested solution works. I have not yet tested it in the proc sql procedure. The adjustment below gave the desired result.
[cid:image001.png@01D579F3.2DA30590]

Re: Proc SQL Loop for days between start_date and end_date

Cugel — Thu, 17 Oct 2019 10:47:29 GMT

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