BookmarkSubscribeRSS Feed
Div_sas
Calcite | Level 5

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.

 

 

3 REPLIES 3
Reeza
Super User

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  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.

 

 


 

Div_sas
Calcite | Level 5
Thank you!
novinosrin
Tourmaline | Level 20
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;

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
  • 3 replies
  • 1573 views
  • 2 likes
  • 3 in conversation