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