我需要一种方法来检查是否找到了一个值,例如"错误",如果找到了,我需要根据另一个值进行分组。我需要查找值3,如果找到值3,那么我需要用相同的ID和Value1对所有行进行分组和标记。
注:我在SAS工作。
请参阅下表:
|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
我需要把上表变成这样:
|id|Value1|Value2 |Value3
|--| --- | --- | ---
|1 | Sta |sta@example.com |Error
|2 |Danny |dany@example.com|NoError
|3 |Elle |elle@example.com|Error
我试过按ID分组的情况,但运气不好。如有任何帮助,我们将不胜感激。干杯
在基本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;
您的描述令人困惑,但输出看起来像是要按ID、VALUE1和VALUE2分组,然后测试该组中的任何观测值是否在VALUE3中有错误。
SAS将布尔表达式计算为1/0(真/假(。因此,组上布尔表达式的MAX((正在测试该表达式是否为真。
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;
结果:
id Value1 Value2 Value3
-------------------------------------------------
1 Sta sta@example.com Error
2 Danny dany@example.com NoError
3 Elle elle@example.com Error
嗨。您可以使用行编号
Select ROW_NUMBER() OVER(Partition by Value3 ORDER BY Value1) AS Row_Number ,
* from YourTable