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

sas-innovate-2024.png

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.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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