topic Re: Horizontal comparision of date variables in SAS Programming

Horizontal comparision of date variables

keen_sas — Fri, 09 Jun 2017 14:23:55 GMT

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

Re: Horizontal comparision of date variables

Reeza — Fri, 09 Jun 2017 14:47:09 GMT

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.

Re: Horizontal comparision of date variables

kiranv_ — Fri, 09 Jun 2017 18:08:59 GMT

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

Re: Horizontal comparision of date variables

Reeza — Fri, 09 Jun 2017 15:15:09 GMT

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

Re: Horizontal comparision of date variables

kiranv_ — Fri, 09 Jun 2017 15:20:47 GMT

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

Re: Horizontal comparision of date variables

Reeza — Fri, 09 Jun 2017 15:28:54 GMT

@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.

Re: Horizontal comparision of date variables

kiranv_ — Fri, 09 Jun 2017 17:12:30 GMT

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;