I have this problem, but in SAS. To use the example provided in this question, I have 5 columns of names (name_1, name_2, etc.), and want to output a list in which the names are listed in descending order of frequency:
John 502
Robert 388
William 387
...
... 1
I took the answer to the question referenced above, and surrounded it with "proc sql;" and "quit;":
proc sql;
create table freqs as
SELECT name, COUNT(1)
FROM ( SELECT name_1 AS name FROM mytable
UNION ALL SELECT name_2 AS name FROM mytable
UNION ALL SELECT name_3 AS name FROM mytable
UNION ALL SELECT name_4 AS name FROM mytable
UNION ALL SELECT name_5 AS name FROM mytable
) AS myunion
GROUP BY name
ORDER BY COUNT(1) DESC
;
quit;
but am getting:
ERROR: Summary functions are restricted to the SELECT and HAVING clauses only.
I am using SAS 9.2.
Thoughts? Thanks for the help!