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
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.
This solution did not work accurately, modified correct answer is below
@kiranv_ doesn't that assume an order for the dates in var1-var8?
@Reeza, I see what you are saying. so this wont work, it just finds the first incidence. Thanks for correcting me.
@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.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.