BookmarkSubscribeRSS Feed
hwangnyc1
Calcite | Level 5

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?

4 REPLIES 4
PaigeMiller
Diamond | Level 26

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
novinosrin
Tourmaline | Level 20

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;
Astounding
PROC Star

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;

PGStats
Opal | Level 21

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

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1075 views
  • 1 like
  • 5 in conversation