topic Re: comparing date column with todays date and output the value from other column in SAS Programming

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

sas121987 — Thu, 17 Apr 2014 19:20:55 GMT

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.

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

ballardw — Thu, 17 Apr 2014 19:32:18 GMT

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.

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

sas121987 — Thu, 17 Apr 2014 19:39:20 GMT

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.

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

ballardw — Thu, 17 Apr 2014 19:57:27 GMT

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?

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

sas121987 — Thu, 17 Apr 2014 20:37:25 GMT

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.

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

ballardw — Thu, 17 Apr 2014 22:09:55 GMT

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?

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

JVarghese — Thu, 17 Apr 2014 22:57:32 GMT

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.

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

Ksharp — Fri, 18 Apr 2014 09:33:54 GMT

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