DATA Step, Macro, Functions and more

comparing date column with todays date and output the value from other column

Reply
Occasional Contributor
Posts: 6

comparing date column with todays date and output the value from other column


Hello I have a data set like this:

************Input cols***************           *****Output col*****

col1_ID  col2_date         col3                       col4_output

100        03/04/2014       0.5

100       04/04/2014        0.6                             0.6

100      05/04/2014         0.3

102      01/05/2014      0.9                                0.9

102     06/08/2014      0.6

102   08/09/2016       0.6

103     02/05/2015      0.6

103     03/04/2014      0.6

103     04/04/2014      0.5                                0.5

103      05/06/2014      0.7

For every ID the date column should be checked with Todays date, if the today date is in between col2_date then I want output col4_output = col3 (value)

Please let me know how to do this.

Thanks.

Super User
Posts: 11,343

Re: comparing date column with todays date and output the value from other column

Posted in reply to sas121987

Some questions:

Is the col2_date variable character or a SAS date value?

You say "today date is in between col2_date" but between implies a value compared with two other values. Between what?

Greater than, less than and/or equal are plausible.

Occasional Contributor
Posts: 6

Re: comparing date column with todays date and output the value from other column

Hi, col2_date is in sas date format.

Today date should be in between col2_date observations, for eg., for  ID 100 observation 2 matches this criteria.

Super User
Posts: 11,343

Re: comparing date column with todays date and output the value from other column

Posted in reply to sas121987

So how does ID 102 get an output? There is only one value so out put it. And what value of "today's date" are you using to to set your output above? Is it 04/04/2014.

It sound like you mean when todays date = col2_date for most of your example. I think you mean within a col1_ID group value. If that is the case, then what is the rule such as for ID 102 when "today's date" value does not appear within the ID group?

Occasional Contributor
Posts: 6

Re: comparing date column with todays date and output the value from other column

Todays date is 04/17/2014, for ID 102, first record satisfy's that condition:  01/05/2014 < today (04/17/2014) <  06/08/2014, so we need the value for col4 here.

Super User
Posts: 11,343

Re: comparing date column with todays date and output the value from other column

Posted in reply to sas121987

So what you actually want is col4 for the largest value of col2_date less than or equal to today's date within each ID group?

Contributor
Posts: 43

Re: comparing date column with todays date and output the value from other column

Posted in reply to sas121987

proc sort data=original;

by col_1ID descending col_2date;

run;

data new (drop=prevdate);

set original;

by col_1ID descending col_2date;

if (first.col_1ID=0) then prevdate=lag(col_2date);

if (col_2date<today()) & ( today() < prevdate ) then col_4=col_3;

run;

May be you can sort it again in reverse order to get the dataset back in the original view. Please try and lets know!

Thanks.

Super User
Posts: 10,046

Re: comparing date column with todays date and output the value from other column

Posted in reply to sas121987

Assuming your data has already sorted by date.

data have;
input col1_ID  col2_date   : mmddyy10.      col3  ;
format col2_date mmddyy10.;
cards;
100        03/04/2014       0.5
100       04/04/2014        0.6           
100      05/04/2014         0.3
102      01/05/2014      0.9                
102     06/08/2014      0.6
102   08/09/2016       0.6
103     02/05/2015      0.6
103     03/04/2014      0.6
103     04/04/2014      0.5                   
103      05/06/2014      0.7
;
run;
%let today='17apr2014'd ;
data want;
 merge have have(firstobs=2 keep=col1_ID col2_date rename=(col1_ID=_id col2_date=_date));
 if (col1_ID eq _id) and ( col2_date lt &today lt _date) then col4=col3;
 drop _: ;
run;





Xia Keshan

Ask a Question
Discussion stats
  • 7 replies
  • 331 views
  • 0 likes
  • 4 in conversation