SAS:选择多列值的频率

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:

我有这个问题，但是在SAS中。为了使用本问题中提供的示例，我有5列名称(name_1、name_2等)，并希望输出一个列表，其中名称按频率降序列出:

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;”和“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.

我正在使用SAS 9.2。

Thoughts? Thanks for the help!

想法吗?谢谢你的帮助!

3 个解决方案

#1

You just need to change your ORDER BY expression to reference the second column. I'd also suggest that you assign the COUNT expression result to a SAS variable name (perhaps "freq"):

您只需要通过表达式更改您的顺序，以引用第二列。我还建议您将计数表达式结果分配给SAS变量名(可能是“freq”):

proc sql; 
   create table freqs as 
   SELECT name
        , COUNT(*) as freq
   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 freq DESC;
quit;

FYI: You can also say ORDER BY 2 DESC to give a relative reference.

附:你也可以用2个DESC的订单作为参考。

#2

Proc SQL does not allow the count(1) in the order by. Try this instead:

Proc SQL不允许订单中的count(1)。试试这个:

proc sql;
    create table freqs as
        SELECT name, COUNT(1) as freqs
        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 2 DESC ;
 quit;

I think it allows the column reference.

我认为它允许列引用。

#3

The following might work as well if the dataset is not too big:

如果数据集不太大，则可以使用以下方法:

data mytable;
 input (name1-name5) (: $17.) @@;
 cards;
 john henry bob jerry james gary bill john mark gabe
 ;
run;

proc sql;
select 'do name = '
||catq("A2SC", name1,name2,name3,name4,name5)
||'; output; end;' into : nlist separated by ' ' from mytable
 ;
quit;

data test;
&nlist
Run;

proc freq order = freq;
tables name;
run;

#1