I need a way to find if a row has a value, and if so then I need to group all rows based on another value

Admin 1
Viewed 113

I need a way to check to see if a value is found e.g., 'Error', and if found then I need to group based on another value. I need to look for Value 3 and if value 3 is found then I need to group and tag all the rows with the same ID and Value1.

Note: I am working in SAS.

Please see the following table:

|id|Value1|Value2          |Value3
|--| ---  | ---            | ---
|1 | Sta  |sta@example.com |Error
|2 |Danny |dany@example.com|
|3 |Elle  |elle@example.com|18
|1 | Sta  |sta@example.com |55
|2 |Danny |dany@example.com|
|3 |Elle  |elle@example.com|Error
|1 | Sta  |sta@example.com |67
|1 | Sta  |sta@example.com |57
|3 |Elle  |elle@example.com|12
|3 |Elle  |elle@example.com|15
|3 |Elle  |elle@example.com|12

I need to turn the above table into this:

|id|Value1|Value2          |Value3
|--| ---  | ---            | ---
|1 | Sta  |sta@example.com |Error
|2 |Danny |dany@example.com|NoError
|3 |Elle  |elle@example.com|Error

I've tried case when and then grouping by ID, but no luck. Any help would be greatly appreciated. Cheers.

proc-sql sas sql
3 Answers

In base SAS:

** Find unique ID/value1/value2 combos with any error **;
proc sort data=have (where=(value3='Error')) out=any_error (keep=id value1 value2) nodupkey; by id value1 value2;

** Keep first occurrence of each ID/value1/value2 combination, assigning value3 to Error if any error in original data, else NoError **;
data want;
   merge have (keep=id value1 value2) any_error (in=in1); by id value1 value2;
   if first.id value1 value2;
   value3 = ifc(in1,'Error','NoError');
run;
user_43858300

Your description is confusing but the output looks like you want to group by ID, VALUE1, and VALUE2 and then test if any observations in that group had Error in VALUE3.

SAS will evaluate boolean expression to 1/0 for True/False. So the MAX() of the boolean expression over a group is testing if the expression is ever true.

proc sql ;
select id, value1, value2 
     , case when (max( value3='Error')) then 'Error' else 'NoError' end as Value3 
from have
group by id, value1, value2
;
quit;

Results:

      id  Value1    Value2                Value3
-------------------------------------------------
       1  Sta       sta@example.com       Error
       2  Danny     dany@example.com      NoError
       3  Elle      elle@example.com      Error
user_49655490

Hi. you can Use Row_Number

Select ROW_NUMBER() OVER(Partition by Value3 ORDER BY Value1) AS Row_Number ,
* from YourTable
user_46032440
Related