DATA Step, Macro, Functions and more

Horizontal comparision of date variables

Reply
Contributor
Posts: 29

Horizontal comparision of date variables

Hi,

 

I have 8 date variables to be comapred with one standard variable and i have to pick the first date variable greater than the standard variable and have to store that existing varaible in new data variable RESULT . 

Belwo is the sample VAR1-VAR4 and VAL1-VAL4 are the variables and STD is the variable to be compared and RESULT is the variable to be stored in it . In between there are missing date values.

 

SUB    VAR1        VAR2              VAR3         VAR4             VAL1            VAL2             VAL3                 VAL4     STD             RESULT
1      2015-02-09  2015-03-08 2015-05-05                       2015-02-18   2015-03-18  2015-05-21                   2015-01-26 2015-02-09
2     2015-02-09  2015-03-08  2015-05-05                       2015-02-18 2015-03-18 2015-05-21                      2015-02-23 2015-03-08
3     2015-02-09 2015-03-08  2015-05-05   2015-02-18 2015-03-18 2015-05-21                                           2015-04-22 2015-02-21
4    2015-01-17 2015-02-14 2015-03-21      2014-12-22 2015-01-27 2015-03-02                      2015-04-15  2015-03-09 2015-03-21
5    2011-10-14                                                                 2011-10-31                                                              2011-10-04 2011-10-31

 

Can any one help me how to idenrtify the first date variable greater than the standard variable.

 

Thanks in advance for your responses

Super User
Posts: 17,905

Re: Horizontal comparision of date variables

If you just need the smallest, use the SMALLEST() function until you find one that's larger than your value. 


If you have a huge amount of data (>1million) you could consider using a binary search method.

PROC Star
Posts: 254

Re: Horizontal comparision of date variables

[ Edited ]

This solution did not work accurately, modified correct answer is below

 

 

Super User
Posts: 17,905

Re: Horizontal comparision of date variables

@kiranv_ doesn't that assume an order for the dates in var1-var8?

PROC Star
Posts: 254

Re: Horizontal comparision of date variables

[ Edited ]

@Reeza, I see what you are saying. so this wont work, it just finds the first incidence. Thanks for correcting me.

Super User
Posts: 17,905

Re: Horizontal comparision of date variables

@kiranv_ use call sort() on the array before implementing? Or use the array with SMALLEST() function to pull out the smallest values? Otherwise your approach is correct.

PROC Star
Posts: 254

Re: Horizontal comparision of date variables

Thanks @Reeza sort was great suggestion. below thing should work now and i have tried with 6 dates with it and it worked

data bde(drop =  i new:);

set abc;
newvar1=var1;
newvar2=var2;
newvar3=var3;
newvar4=var4;
newvar5=var5;
newvar6=var6;
array vare(*) var1-var6;
array new_vare(*) newvar1-newvar6;
call sortn(of new_vare[*]);
do i = 1 to 6 until(standard<new_vare{i});

if standard>new_vare{i} then maxval = .;

else maxval = new_vare{i};

end;

format maxval date9.;

run;
Ask a Question
Discussion stats
  • 6 replies
  • 114 views
  • 2 likes
  • 3 in conversation