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
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
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
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.