I have a datset that has multiple observation per subject ID.
Have:
ID status
001 completed
001 failed
001 failed
002 completed
002 completed
002 partially completed
002 failed
003 failed
003 failed
003 failed
From the above data I want to create a dataset with one record per subject and create a new variable(allfail) which has a value of '1' if all the observation for that subject have a 'failed' status.
Want:
ID allfail
001 .
002 .
003 1
Currently I am counting the total number of observation for each subject and then checking to see if is equal to count of distinct status for each subject. Wanted to see if there is a more efficient way to do this? Thank you.
SQL is great for these type of problems.
The logic here is to check if the max = min of the column which means all values are the same, and then to check if it equals the failed value. You could also do this in a data step using First/Last logic and RETAIN.
proc sql;
create table want as
select ID, case when max(status)=min(status) and max(status) = 'failed' then 1
else . end as all_failed_flag
from have
group by ID;
quit;
proc print data=want;
run;
@Div_sas wrote:
I have a datset that has multiple observation per subject ID.
Have:
ID status
001 completed
001 failed
001 failed
002 completed
002 completed
002 partially completed
002 failed
003 failed
003 failed
003 failedFrom the above data I want to create a dataset with one record per subject and create a new variable(allfail) which has a value of '1' if all the observation for that subject have a 'failed' status.
Want:
ID allfail
001 .
002 .
003 1
Currently I am counting the total number of observation for each subject and then checking to see if is equal to count of distinct status for each subject. Wanted to see if there is a more efficient way to do this? Thank you.
data have;
input ID $ status & $20.;
datalines;
001 completed
001 failed
001 failed
002 completed
002 completed
002 partially completed
002 failed
003 failed
003 failed
003 failed
;
proc sql;
create table want as
select id,sum(status='failed')=count(status) as allfail
from have
group by id;
quit;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.