BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ger15xxhcker
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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


 

View solution in original post

1 REPLY 1
Reeza
Super User

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


 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 775 views
  • 0 likes
  • 2 in conversation