if all the values in the same by a group

[ger15xxhcker]

Hi all!

I ' d like to know , all the values in a group (group=id1 and id2) is the same or not.

if id=1 and id2=a then FREQ = MONTH and DAY then 'ERR'. if all the same in the group then FREQ='ERR'.

sample:

data have;
input id1 $1. id2 $1. FREQ $5. ;
cards;
0 w YEAR
1 a	MONTH
1 a MONTH
1 a DAY
1 a DAY
1 a DAY
1 b MONTH
2 c WEEK
2 c WEEK
2 c WEEK
3 d WEEK
3 d DAY
3 a WEEK
;
run;

data want;
input id1 $1. id2 $1. FREQ $5. FLAG $5.;
cards;
0 w YEAR 	YEAR
1 a	MONTH	ERR
1 a MONTH	ERR
1 a DAY		ERR
1 a DAY 	ERR
1 a DAY 	ERR
1 b MONTH	MONTH
2 c WEEK	WEEK
2 c WEEK 	WEEK
2 c WEEK	WEEK
3 d WEEK	ERR
3 d DAY		ERR
3 a WEEK	ERR
;
run;

thanks for all

[Reeza · Accepted solution]

SQL is good for this one, since it re-merges automatically and you can use MAX/MIN on a character column. If the maximum is different from the minimum you have different values so your flag is ERR. 

Otherwise the flag is set to the FREQ value. 

proc sql;
create table want as
select *, 
      case when max(freq) ne min(freq) then 'ERR'
         else FREQ 
      end as FLAG
from have
group by id1, id2;
quit;

---

@ger15xxhcker wrote:

Hi all!

I ' d like to know , all the values in a group (group=id1 and id2) is the same or not.

if id=1 and id2=a then FREQ = MONTH and DAY then 'ERR'. if all the same in the group then FREQ='ERR'.

sample:

data have;
input id1 $1. id2 $1. FREQ $5. ;
cards;
0 w YEAR
1 a	MONTH
1 a MONTH
1 a DAY
1 a DAY
1 a DAY
1 b MONTH
2 c WEEK
2 c WEEK
2 c WEEK
3 d WEEK
3 d DAY
3 a WEEK
;
run;

data want;
input id1 $1. id2 $1. FREQ $5. FLAG $5.;
cards;
0 w YEAR 	YEAR
1 a	MONTH	ERR
1 a MONTH	ERR
1 a DAY		ERR
1 a DAY 	ERR
1 a DAY 	ERR
1 b MONTH	MONTH
2 c WEEK	WEEK
2 c WEEK 	WEEK
2 c WEEK	WEEK
3 d WEEK	ERR
3 d DAY		ERR
3 a WEEK	ERR
;
run;

thanks for all

---