DATA Step, Macro, Functions and more

Looking for shortest date differences

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 96
Accepted Solution

Looking for shortest date differences

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. 

Swain

Accepted Solutions
Solution
‎11-03-2016 02:14 PM
Super User
Posts: 10,511

Re: Looking for shortest date differences

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;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,385

Re: Looking for shortest date differences

[ Edited ]

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;



 

Solution
‎11-03-2016 02:14 PM
Super User
Posts: 10,511

Re: Looking for shortest date differences

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;
Frequent Contributor
Posts: 96

Re: Looking for shortest date differences

[ Edited ]

Hi there,

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

 

Swain
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 237 views
  • 1 like
  • 3 in conversation