BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Pysiek
Obsidian | Level 7

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.

 

PATNOILLNESSDATE DAY
1HEADACHE01-Feb-20 1
1HEADACHE02-Feb-20 2
1HEADACHE03-Feb-20 3
1HEADACHE04-Feb-20 4
1HEADACHE05-Feb-20 5
1HEADACHE06-Feb-20 6
1HEADACHE07-Feb-20 7
1HEADACHE09-Feb-20 8
1RASH04-Feb-20 1
1RASH05-Feb-20 2
1RASH06-Feb-20 3
1RASH07-Feb-20 4
1RASH08-Feb-20 5
1RASH09-Feb-20 6
1RASH10-Feb-20 7
1RASH11-Feb-20 8
1RASH12-Feb-20 9
1RASH13-Feb-20 10
1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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;

View solution in original post

8 REPLIES 8
novinosrin
Tourmaline | Level 20

Hi @Pysiek  Are you asking for-

 

data want;
 set have;
by id illness notsorted;
if first.illness then day=1;
else day+1;
run;

 

Pysiek
Obsidian | Level 7
I would like to calculate difference between two dates. This is a perfect scenario there could be multiple gaps and more than 10 illness for subject. Lets say that I want to have fromdate and todate. From date would be start date (retianed?) and todate would be next date (lag?).
novinosrin
Tourmaline | Level 20

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?

Pysiek
Obsidian | Level 7

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 :

 

abc.png

 

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!

Reeza
Super User
Do you really want every row though or just the last row?

Reeza
Super User

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 :

 

abc.png

 

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!


 

novinosrin
Tourmaline | Level 20

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;

SAS Innovate 2025: Call for Content

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!

Submit your idea!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 8 replies
  • 981 views
  • 3 likes
  • 3 in conversation