BookmarkSubscribeRSS Feed
keen_sas
Quartz | Level 8

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

6 REPLIES 6
Reeza
Super User

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.

kiranv_
Rhodochrosite | Level 12

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

 

 

Reeza
Super User

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

kiranv_
Rhodochrosite | Level 12

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

Reeza
Super User

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

kiranv_
Rhodochrosite | Level 12

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 784 views
  • 2 likes
  • 3 in conversation