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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.