DATA Step, Macro, Functions and more

Looking for shortest date differences

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 104
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: 11,343

Re: Looking for shortest date differences

Posted in reply to DeepakSwain

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,554

Re: Looking for shortest date differences

[ Edited ]
Posted in reply to DeepakSwain

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: 11,343

Re: Looking for shortest date differences

Posted in reply to DeepakSwain

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: 104

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
  • 260 views
  • 1 like
  • 3 in conversation