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
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;
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.