I’m trying to determine cases who were in as of 5-10-2017.
Here is the data that I have:
id | status | date |
1 | In | 5/14/2015 |
1 | out | 6/15/2016 |
2 | in | 3/3/2013 |
2 | out | 5/22/2014 |
2 | in | 1/3/2015 |
2 | out | 3/3/2015 |
3 | in | 1/8/2016 |
4 | in | 6/6/2016 |
4 | out | 5/31/2017 |
And the table that I want:
id | InAsof51017 |
1 | no |
2 | no |
3 | yes |
4 | yes |
Any suggestions as to how to get this?
UNTESTED CODE
proc sql;
create table want as select i.id,i.date as in_date,o.date as out_date,
case when '10MAY2017'd >= i.date and '10MAY2017'd <= o.date then 'yes'
else 'no' end as in_or_out
from have(where=(status='In')) as i left join have(where=(status='out')) as o
on i.id=o.id order by i.id;
quit;
Assuming i understood correctly:
data have;
input id status $ date :mmddyy10.;
format date mmddyy10.;
cards;
1 In 5/14/2015
1 out 6/15/2016
2 in 3/3/2013
2 out 5/22/2014
2 in 1/3/2015
2 out 3/3/2015
3 in 1/8/2016
4 in 6/6/2016
4 out 5/31/2017
;
/*51017*/
data want;
set have;
by id;
if last.id;
length InAsof51017 $8;
InAsof51017='YES';
if upcase(status)="OUT" and date<='10may2017'd then InAsof51017='NO';
run;
I would try:
data want;
set have;
by id;
if first.id then InAsof51017 = 'yes';
retain InAsof51017;
status = upcase(status);
if status = 'IN' and date > '05May2017'd then InAsof51017 = 'no';
else if status = 'OUT' and date <= '05May2017'd then InAsof51017 = 'no';
if last.id;
keep id InAsof51017;
run;
A datastep solution:
proc sort data=have; by id date; run;
data want;
length inAsOf $3;
do until(last.id);
set have; by id;
if status = "in" then inDate = date;
if status = "out" then do;
if missing(inAsOf) and inDate <= '10MAY2017'd < date then inAsOf = "Yes";
call missing(inDate);
end;
end;
if missing(inAsOf) then do;
if missing(inDate) then inAsOf = "No";
else if inDate > '10MAY2017'd then inAsOf = "No";
else inAsOf = "Yes";
end;
drop inDate status date;
run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.