I want to calculate maximum number of times a value appear by group. For example, in the table below, 11 appears thrice in a group value of 1 and 22 appears twice in a group value of 1. So i want 1 11 in a row as 11 appears maximum times. Same logic holds for a ID value of 2. In that case, 12 22 should come out in a row. In the final output table, i need 2 rows as explained above.
ID | ID2 |
1 | 11 |
1 | 11 |
1 | 11 |
1 | 22 |
1 | 22 |
2 | 12 |
2 | 12 |
2 | 12 |
2 | 16 |
Here is quick & dirty SQL:
proc sql;
select distinct id,id2 from
(select id,id2,count(*) as ct from have group by id,id2)
group by id
having ct=max(ct)
;
quit;
What do you want to do if two or more values of ID2 appear the same number of times such as:
ID | ID2 |
1 | 11 |
1 | 11 |
1 | 11 |
1 | 22 |
1 | 22 |
1 | 22 |
2 | 12 |
2 | 12 |
2 | 16 |
Would you want 1 11 or 1 22? And the logic between picking the two?
(NOTE: I modified your data so 1 11 and 1 22 both appear 3 times)
Here is quick & dirty SQL:
proc sql;
select distinct id,id2 from
(select id,id2,count(*) as ct from have group by id,id2)
group by id
having ct=max(ct)
;
quit;
It is possible to achieve the same results using data step, Hash or DOW can be utilized. The following code is an example using 2XDOW:
data have;
input ID ID2;
cards;
1 11
1 11
1 11
1 22
1 22
1 22
2 12
2 12
2 16
;
data want;
do until (last.id);
set have;
by id id2;
if first.id then
call missing (_id,_id2);
if first.id2 then
do;
_id=max(_id,_id2);
_id2=1;
end;
else _id2+1;
end;
do until (last.id);
set have;
by id id2;
if first.id2 then
do;
_id2=1;
end;
else _id2+1;
if last.id2 and _id2=_id then
output;
end;
drop _:;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.