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

Question

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.

It is unclear what you are asking for. Are you trying to find the set of unique values of ID, VALUE1, and VALUE2 and generate VALUE3 as either ERROR or NOERROR based on whether that group ever had any errors? — Tom, May 21 '21 at 11:55
Yes! Essentially I’m trying to find if the group ever had an error and if they did then I want to tag all of them with error. — , May 21 '21 at 15:17

score 2 · Answer 1 · answered May 22 '21 at 18:43

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;

score 1 · Accepted Answer · answered May 21 '21 at 12:01

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

score -1 · Answer 3 · answered May 21 '21 at 05:09

-1

Hi. you can Use Row_Number

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

answered May 21 '21 at 05:09

Ali NajafZadeh

51
7
20

4

PROC SQL does not support windowing functions and even if it did it is not clear how this would help with the problem. – Tom May 21 '21 at 12:04

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

3 Answers3