topic Re: How to calculate days based on dates in one variable. in New SAS User

How to calculate days based on dates in one variable.

Pysiek — Mon, 31 Aug 2020 19:45:11 GMT

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

Re: How to calculate days based on dates in one variable.

Reeza — Mon, 31 Aug 2020 20:04:40 GMT

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

Re: How to calculate days based on dates in one variable.

novinosrin — Mon, 31 Aug 2020 20:14:41 GMT

Hi @Pysiek Are you asking for-

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

Re: How to calculate days based on dates in one variable.

Pysiek — Mon, 31 Aug 2020 20:32:46 GMT

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?).

Re: How to calculate days based on dates in one variable.

novinosrin — Mon, 31 Aug 2020 20:45:21 GMT

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?

Re: How to calculate days based on dates in one variable.

Pysiek — Mon, 31 Aug 2020 21:38:47 GMT

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!

Re: How to calculate days based on dates in one variable.

Reeza — Mon, 31 Aug 2020 22:08:16 GMT

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

Re: How to calculate days based on dates in one variable.

Reeza — Mon, 31 Aug 2020 22:15:44 GMT

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!

Re: How to calculate days based on dates in one variable.

novinosrin — Mon, 31 Aug 2020 23:08:07 GMT

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;