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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.