BookmarkSubscribeRSS Feed
sas121987
Calcite | Level 5


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.

7 REPLIES 7
ballardw
Super User

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.

sas121987
Calcite | Level 5

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.

ballardw
Super User

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?

sas121987
Calcite | Level 5

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.

ballardw
Super User

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?

JVarghese
Obsidian | Level 7

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.

Ksharp
Super User

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

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
  • 7 replies
  • 1202 views
  • 0 likes
  • 4 in conversation