flagg certain locations

[pacman94]

I would like to create a flag if there are instances for "acute" locations more than once

group	index	location
1	1	Home
1	2	Acute
1	3	Rehab
1	4	Home
1	5	Acute
2	1	Acute
2	2	Home
2	3	Rehab
3	1	Home
3	2	Rehab
3	3	Home
4	1	Home
4	2	Acute
4	3	Home
4	4	Acute
4	5	Rehab

Want:

group	index	location	rehospitalization
1	1	Home	0
1	2	Acute	0
1	3	Rehab	0
1	4	Home	0
1	5	Acute	1
2	1	Acute	0
2	2	Home	0
2	3	Rehab	0
3	1	Home	0
3	2	Rehab	0
3	3	Home	0
4	1	Home	0
4	2	Acute	0
4	3	Home	0
4	4	Acute	1
4	5	Rehab	0

or

group	rehospitalization
1	1
2	0
3	0
4	1

What I have done so far  is this:

proc sql;

create table test2 as

select distinct group, location, (*) as total_revisits

from test;

quit;

data test2; set test2;

if total_revisits >= 2 then rehopsitalization = 1

;run;

i was wondering if there was a simple way

[Kurt_Bremser]

Use a RETAINed variable in a data step:

data have;
input group index location $;
datalines;
1 1 Home
1 2 Acute
1 3 Rehab
1 4 Home
1 5 Acute
2 1 Acute
2 2 Home
2 3 Rehab
3 1 Home
3 2 Rehab
3 3 Home
4 1 Home
4 2 Acute
4 3 Home
4 4 Acute
4 5 Rehab
;

data want;
set have;
by group;
retain has_acute;
if first.group then has_acute = 0;
rehospitalization = 0;
if location = "Acute"
then do;
  if has_acute
  then rehospitalization = 1;
  has_acute = 1;
end;
drop has_acute;
run;

[ballardw]

A minor variation on @Kurt_Bremser's solution.

Example data includes an example with a third "Acute" to see if the result is as needed in that case.

data have;
  input group	index	location $;
datalines;
1	1	Home
1	2	Acute
1	3	Rehab
1	4	Home
1	5	Acute
1	6	Rehab
1	7	Home
1	8	Acute
2	1	Acute
2	2	Home
2	3	Rehab
3	1	Home
3	2	Rehab
3	3	Home
4	1	Home
4	2	Acute
4	3	Home
4	4	Acute
4	5	Rehab
;

data want;
   set have;
   by group;
   retain acutecount;
   if first.group then acutecount=0;
   if location='Acute' then acutecount+1;
   Flag= (location ='Acute' and acutecount>1);
   drop acutecount;
run;

SAS returns a numeric 1 for a true comparison and 0 for false. So the Flag= statement is just another way to do a sort of if <condition> then value=1; else value=0;

[Ksharp · Accepted solution]

data have;
  input group	index	location $;
datalines;
1	1	Home
1	2	Acute
1	3	Rehab
1	4	Home
1	5	Acute
1	6	Rehab
1	7	Home
1	8	Acute
2	1	Acute
2	2	Home
2	3	Rehab
3	1	Home
3	2	Rehab
3	3	Home
4	1	Home
4	2	Acute
4	3	Home
4	4	Acute
4	5	Rehab
;
proc sql;
create table want as
select group,sum(location='Acute')>1 as rehospitalization
 from have
  group by group;
quit;