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