DATA Step, Macro, Functions and more

Determine standing from two columns

Reply
Frequent Learner
Posts: 1

Determine standing from two columns

I’m trying to determine cases who were in as of 5-10-2017.

Here is the data that I have:

 

idstatusdate
1In5/14/2015
1out6/15/2016
2in3/3/2013
2out5/22/2014
2in1/3/2015
2out3/3/2015
3in1/8/2016
4in6/6/2016
4out5/31/2017

 

 

And the table that I want:

 

idInAsof51017
1no
2no
3yes
4yes

 

 

 

Any suggestions as to how to get this?

Respected Advisor
Posts: 2,802

Re: Determine standing from two columns

[ Edited ]
Posted in reply to hwangnyc1

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;

 

--
Paige Miller
PROC Star
Posts: 1,558

Re: Determine standing from two columns

Posted in reply to hwangnyc1

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;
Super User
Posts: 6,626

Re: Determine standing from two columns

Posted in reply to hwangnyc1

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;

Esteemed Advisor
Posts: 5,475

Re: Determine standing from two columns

Posted in reply to hwangnyc1

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;
PG
Ask a Question
Discussion stats
  • 4 replies
  • 86 views
  • 1 like
  • 5 in conversation