topic Re: Determine standing from two columns in SAS Programming

Determine standing from two columns

hwangnyc1 — Thu, 31 May 2018 18:57:09 GMT

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?

Re: Determine standing from two columns

PaigeMiller — Thu, 31 May 2018 19:11:31 GMT

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;

Re: Determine standing from two columns

novinosrin — Thu, 31 May 2018 19:13:46 GMT

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;

Re: Determine standing from two columns

Astounding — Thu, 31 May 2018 19:33:42 GMT

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;

Re: Determine standing from two columns

PGStats — Thu, 31 May 2018 19:46:39 GMT

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;