topic Re: Calculate time difference when certain requirement met in SAS Programming

Calculate time difference when certain requirement met

huhuhu — Wed, 17 Jun 2020 22:06:14 GMT

Hello,

I got stuck in the process to calculate the difference between two dates under a specific requirement. My data looks like below (Diff and Count are the variables that I desire to have)

ID Date FirstPositive Diff Count

A 1/1/2020 . . .

A 1/3/2020 1 . 1

A 1/4/2020 . 1 2

A 1/5/2020 . 1 3

B 1/4/2020 1 . 1

B 1/5/2020 . 1 2

I want to calculate the difference between dates for each ID, but start from the row when FirstPositive =1. I also would like to have the count variable which count the number of rows for each ID start from the row when FirstPositive =1.

Any advice would be appreciated!

Thank you!

Re: Calculate time difference when certain requirement met

Jagadishkatam — Wed, 17 Jun 2020 22:29:20 GMT

Please try the below code

data have;
input ID$ Date:mmddyy10. FirstPositive;
cards;
A 1/1/2020 .         
A 1/3/2020 1          
A 1/4/2020 .         
A 1/5/2020 .         
B 1/4/2020 1          
B 1/5/2020 .        
;
 
data want;
set have;
by id notsorted;
retain FirstPositive2;
if first.id then do;FirstPositive2=.;count=.;end;
if FirstPositive ne . then FirstPositive2=FirstPositive;
count+FirstPositive2;
if FirstPositive2 ne . then diff=Date-lag(date);
if first.id then diff=.;
run;

Re: Calculate time difference when certain requirement met

ballardw — Wed, 17 Jun 2020 22:31:28 GMT

See if this gets you started.

data have;
   input ID $  Date :mmddyy10.  FirstPositive;
   format date mmddyy10.;
datalines;
A      1/1/2020        .    
A      1/3/2020      1     
A      1/4/2020       .    
A      1/5/2020       .    
B      1/4/2020      1     
B      1/5/2020       .    
;

data want;
   set have;
   by id;
   retain count  posflag  ;
   difdate= dif(date);
   if first.id then call missing(count,posflag);
   if posflag then diff=difdate;
   if firstPositive=1 then posflag=1;
   if posflag then count+1;
   drop posflag difdate;
run;

You may have to sort your data set by ID and Date prior to the Want data set.

If you have not seen these functions before:

Retain keeps variable values from iteration of the data step to the next.

DIF is a function that returns the current value of a variable minus the previous value.

When using BY statement SAS creates automatic variables First. and Last. that indicate whether the current is the first or last that level of a by variable.

Call missing is a function that can set a number of variables to missing values.

Timing of calculations is the main part of this problem with the when to set the diff value in relation to the iteration of the count.

Re: Calculate time difference when certain requirement met

smantha — Wed, 17 Jun 2020 22:59:39 GMT

data sample(Drop = diff count);
informat ID $3. Date mmddyy10. FirstPositive Diff Count 3.;
input ID $3. Date FirstPositive Diff Count;
format date mmddyy10.;
datalines;
A 1/1/2020 . . .
A 1/3/2020 1 . 1 
A 1/4/2020 . 1 2
A 1/5/2020 . 1 3
B 1/4/2020 1 . 1
B 1/5/2020 . 1 2
;
proc sort; by ID Date; run;

data sample(drop=prv_date);
set sample;
by id;
retain count Diff prv_date 0;
if first.id then do;
count=.; Diff=.;
prv_date = .;
end;

if FirstPositive = 1 then do;
count=1;
prv_date = Date;
end;
else if count > 0 then do;
count = count+1;
Diff = Date-prv_date;
prv_date=date;
end;
run;

Re: Calculate time difference when certain requirement met

novinosrin — Wed, 17 Jun 2020 23:38:23 GMT

Hi @huhuhu Your case presents a nice scenario for yet another "dorfmanisms" aka automatic variables usage-

data have;
   input ID $  Date :mmddyy10.  FirstPositive;
   format date mmddyy10.;
datalines;
A      1/1/2020        .    
A      1/3/2020      1     
A      1/4/2020       .    
A      1/5/2020       .    
B      1/4/2020      1     
B      1/5/2020       .    
;

data want;
 do until(last.id);
  set have;
  by id;
  diff=dif(date);
  if _n_ then diff=.;
  if FirstPositive then _n_=0;
  if _n_=0  then  count=sum(count,1);
  output;
 end;
run;

Re: Calculate time difference when certain requirement met

r_behata — Wed, 17 Jun 2020 23:55:01 GMT

data have;
input ID  $ Date : mmddyy10.  FirstPositive;
format date mmddyy10.;
cards;
A 1/1/2020 .
A 1/3/2020 1
A 1/4/2020 .
A 1/5/2020 .
B 1/4/2020 1
B 1/5/2020 .
;
run;



data want;
	Set have;
	by ID FirstPositive notsorted;

	retain FirstPositive_;

	if FirstPositive=1 then FirstPositive_=FirstPositive;

	if first.id then count=.;
		if FirstPositive_=1  then count+ (1 * FirstPositive_);	else count =.;

	dif=ifn(first.ID=0 and count > FirstPositive_ ,dif(date),.);

	drop FirstPositive_ ;
run;

Re: Calculate time difference when certain requirement met

huhuhu — Thu, 18 Jun 2020 00:09:01 GMT

Thank you so much. That's exactly what I'm looking for. I also appreciate your detailed explanation of functions used here. so helpful!