Hi guys,
I would like to calculate how many days there was from 1st date till last date. My first date for HEADACHE would be 01Feb2020 and first date for RASH would be 4Feb2020. I would like to know for how many days somebody had illness. Please notice that there is a gap between 07Feb and 09Feb for HEADACHE which should be calculated as 1 day - not 2. (like in DAY). What is the best method to program this? Any explanation much appreciated.
PATNO | ILLNESS | DATE | DAY | |
1 | HEADACHE | 01-Feb-20 | 1 | |
1 | HEADACHE | 02-Feb-20 | 2 | |
1 | HEADACHE | 03-Feb-20 | 3 | |
1 | HEADACHE | 04-Feb-20 | 4 | |
1 | HEADACHE | 05-Feb-20 | 5 | |
1 | HEADACHE | 06-Feb-20 | 6 | |
1 | HEADACHE | 07-Feb-20 | 7 | |
1 | HEADACHE | 09-Feb-20 | 8 | |
1 | RASH | 04-Feb-20 | 1 | |
1 | RASH | 05-Feb-20 | 2 | |
1 | RASH | 06-Feb-20 | 3 | |
1 | RASH | 07-Feb-20 | 4 | |
1 | RASH | 08-Feb-20 | 5 | |
1 | RASH | 09-Feb-20 | 6 | |
1 | RASH | 10-Feb-20 | 7 | |
1 | RASH | 11-Feb-20 | 8 | |
1 | RASH | 12-Feb-20 | 9 | |
1 | RASH | 13-Feb-20 | 10 |
Hi again @Pysiek Please try this-
data have;
input PATNO ILLNESS :$10. DATE :date11.;* DAY;
format date date11.;
cards;
1 HEADACHE 01-Feb-20 1
1 HEADACHE 02-Feb-20 2
1 HEADACHE 03-Feb-20 3
1 HEADACHE 04-Feb-20 4
1 HEADACHE 05-Feb-20 5
1 HEADACHE 06-Feb-20 6
1 HEADACHE 07-Feb-20 7
1 HEADACHE 09-Feb-20 8
1 RASH 04-Feb-20 1
1 RASH 05-Feb-20 2
1 RASH 06-Feb-20 3
1 RASH 07-Feb-20 4
1 RASH 08-Feb-20 5
1 RASH 09-Feb-20 6
1 RASH 10-Feb-20 7
1 RASH 11-Feb-20 8
1 RASH 12-Feb-20 9
1 RASH 13-Feb-20 10
;
data want;
set have;
by PATNO ILLNESS;
if first.illness then day=1;
else day+1;
k=lag(date);
if first.ILLNESS then tot_day=1;
else tot_day+ intck('day',k,date);
drop k;
run;
Hi @Pysiek Are you asking for-
data want;
set have;
by id illness notsorted;
if first.illness then day=1;
else day+1;
run;
Hmm I think I am missing something here. In your example, you mentioned a gap to be construed as 1 day?? Can you post a better example to understand the gaps and the logic for handling gaps plz?
Yeah maybe its a bit complicated. I would like to have two new variables.
1) DAY - like in example below. So i want to calculate how many days there was.
2) TOT_DAY - based on dates i would like to calculate how long somebody had illness. Example below :
PATNO 1 on HEADACH had 8 days but HEADACHE occured from 1st FEB to 9th FEB which means that it took 9 days in total.
Hope that this will help you understood what Im trying to do 🙂
Many thanks!
Assuming you want only the last row, I would do something like the following, which is based on what you've shown so far.
proc sql;
create table want as
select patno, illness, max(date) - min(date) as duration, count(distinct date) as num_days
from have
group by patno, illness
order by patno, illness;
quit;
@Pysiek wrote:
Yeah maybe its a bit complicated. I would like to have two new variables.
1) DAY - like in example below. So i want to calculate how many days there was.
2) TOT_DAY - based on dates i would like to calculate how long somebody had illness. Example below :
PATNO 1 on HEADACH had 8 days but HEADACHE occured from 1st FEB to 9th FEB which means that it took 9 days in total.
Hope that this will help you understood what Im trying to do 🙂
Many thanks!
Hi again @Pysiek Please try this-
data have;
input PATNO ILLNESS :$10. DATE :date11.;* DAY;
format date date11.;
cards;
1 HEADACHE 01-Feb-20 1
1 HEADACHE 02-Feb-20 2
1 HEADACHE 03-Feb-20 3
1 HEADACHE 04-Feb-20 4
1 HEADACHE 05-Feb-20 5
1 HEADACHE 06-Feb-20 6
1 HEADACHE 07-Feb-20 7
1 HEADACHE 09-Feb-20 8
1 RASH 04-Feb-20 1
1 RASH 05-Feb-20 2
1 RASH 06-Feb-20 3
1 RASH 07-Feb-20 4
1 RASH 08-Feb-20 5
1 RASH 09-Feb-20 6
1 RASH 10-Feb-20 7
1 RASH 11-Feb-20 8
1 RASH 12-Feb-20 9
1 RASH 13-Feb-20 10
;
data want;
set have;
by PATNO ILLNESS;
if first.illness then day=1;
else day+1;
k=lag(date);
if first.ILLNESS then tot_day=1;
else tot_day+ intck('day',k,date);
drop k;
run;
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.