How to calculate days based on dates in one variable.

[Pysiek]

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

[Reeza]

Looks like you want a counter variable.
https://stats.idre.ucla.edu/sas/faq/how-can-i-create-an-enumeration-variable-by-groups/

[novinosrin]

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]

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]

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]

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!

[Reeza]

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

[Reeza]

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!

---

[novinosrin · Accepted solution]

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;