topic Re: Looking for shortest date differences in SAS Programming

Looking for shortest date differences

DeepakSwain — Thu, 03 Nov 2016 16:46:57 GMT

Hi there,

I am looking for the shortest positive date difference. But if the date difference value is negative and is smaller than shortest postive date difference then I want the negative difference .

In other word, out of the following date differences: 5, 2, -3: I want to flag 2 [as 2 is shortest positive and is less than (-)3]

whereas out of the following date differences: 5, 4, -3: I want to flag -3 [as 4 is shortest but is more than (-)3 ] .

I am having difficult at my final step of my SAS code.

data test1;
input id  date1 $9. date2 $9.  ;
cards;
1 11012016 13012016 
1 11012016 15012016
1 11012016 10012016
1 11012016 11012016
2 11012016 20012016
2 11012016 15012016
2 11012016 16012016
2 11012016 08012016

;
run;


data test2;
set test1 (rename= (date1=date3 date2=date4));
date1=input(put(date3, 8.), ddmmyy8.);
date2=input(put(date4, 8.), ddmmyy8.);
format date1 date2 date9.;
date_diff=intck('DAY', date1, date2);
run;

proc sort data =test2 ; by id date_diff; run;

Can somebody help me with this. Thank you in advance for your kind reply.

Re: Looking for shortest date differences

Shmuel — Thu, 03 Nov 2016 18:18:36 GMT

As sas dates are the days past '01JAN1960' you don't need the INTCK function but just DATE2-DATE1 to get the gap days;

In oredre to get tha absolute minimum value use ABS function:

data test1;
input id  date1 $9. date2 $9.  ;
cards;
1 11012016 13012016 
1 11012016 15012016
1 11012016 10012016
1 11012016 11012016
2 11012016 20012016
2 11012016 15012016
2 11012016 16012016
2 11012016 08012016

;
run;

data test2;
 set test1;
     date_diff = abs(date2 - date1);
     format date1 date2 date9.;
run;

you can display in the log the line of minimum date_diff by:

data test2;
 set test1 end=eof;
     retain min_diff  n_diff;
     date_diff = abs(date2 - date1);
     format date1 date2 date9.;
     if _N_ = 1 then do; min_diff = date_diff; n_diff=1; end;
     else do; min_diff = min(min_diff, date_diff); n_diff = _N_; end;

     if eof then put: ">>> Minimun date_dif is in row " n_diff;
run;

      
run;

Re: Looking for shortest date differences

ballardw — Thu, 03 Nov 2016 17:54:19 GMT

Can you show what you actually want for output? "Flag" can mean different things to different people. Do want to ADD a variable or select records or something else?

Also I believe that what you are asking for with:

" am looking for the shortest positive date difference. But if the date difference value is negative and is smaller than shortest postive date difference then I want the negative difference ."

Is the smallest absolute value of the difference ABS(3-5) = ABS(-2) = 2.

data test1;
   informat id best4. date1 date2 ddmmyy10.;
   format date1 date2 date9.;
   input id  date1 date2  ;
   date_diff=abs(intck('DAY', date1, date2));

cards;
1 11012016 13012016 
1 11012016 15012016
1 11012016 10012016
1 11012016 11012016
2 11012016 20012016
2 11012016 15012016
2 11012016 16012016
2 11012016 08012016
;
run;

proc sort data=test1;
   by id date_diff;
run;

data want;
   set test1;
   by id date_diff;
   flag = (first.id);
run;

Re: Looking for shortest date differences

DeepakSwain — Thu, 03 Nov 2016 18:23:21 GMT

Hi there,

Thank you for reading my mind and providing me exactly what I was looking for.