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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.