频率不相加(SAS PROC SQL)

Question

我只想找到唯一的身份证号码的频率。我试过PROC，但不知道如何做SAS等效的SELECT DISTINCT是什么。我运行了下面的代码，得到了不加起来的数字。

代码：PROC SQL; SELECT COUNT (DISTINCT MOTPID) FROM WORK.'0__1_MOTP_COMMENTS_0000'n;

结果: 20599

代码：

PROC SQL;
SELECT COUNT (DISTINCT MOTPID) FROM WORK.'0__1_MOTP_COMMENTS_0000'n
WHERE MOTPComponentDescription = '1a (obs): Demonstrating knowledge of content and pedagogy';

PROC SQL;
SELECT COUNT (DISTINCT MOTPID) FROM WORK.'0__1_MOTP_COMMENTS_0000'n
WHERE MOTPComponentDescription = '1a (p&p): Demonstrating knowledge of content and pedagogy';

PROC SQL;
SELECT COUNT (DISTINCT MOTPID) FROM WORK.'0__1_MOTP_COMMENTS_0000'n
WHERE MOTPComponentDescription = '1e (obs): Designing coherent instruction';

PROC SQL;
SELECT COUNT (DISTINCT MOTPID) FROM WORK.'0__1_MOTP_COMMENTS_0000'n
WHERE MOTPComponentDescription = '1e (p&p): Designing coherent instruction';

PROC SQL;
SELECT COUNT (DISTINCT MOTPID) FROM WORK.'0__1_MOTP_COMMENTS_0000'n
WHERE MOTPComponentDescription = '2a: Creating an environment of respect and rapport';

PROC SQL;
SELECT COUNT (DISTINCT MOTPID) FROM WORK.'0__1_MOTP_COMMENTS_0000'n
WHERE MOTPComponentDescription = '2d: Managing student behavior';

PROC SQL;
SELECT COUNT (DISTINCT MOTPID) FROM WORK.'0__1_MOTP_COMMENTS_0000'n
WHERE MOTPComponentDescription = '3b: Using questioning and discussion techniques';

PROC SQL;
SELECT COUNT (DISTINCT MOTPID) FROM WORK.'0__1_MOTP_COMMENTS_0000'n
WHERE MOTPComponentDescription = '3c: Engaging students in learning';

PROC SQL;
SELECT COUNT (DISTINCT MOTPID) FROM WORK.'0__1_MOTP_COMMENTS_0000'n
WHERE MOTPComponentDescription = '3d: Using assessment in instruction';

PROC SQL;
SELECT COUNT (DISTINCT MOTPID) FROM WORK.'0__1_MOTP_COMMENTS_0000'n
WHERE MOTPComponentDescription = '4e (obs): Growing and developing     professionally';

PROC SQL;
SELECT COUNT (DISTINCT MOTPID) FROM WORK.'0__1_MOTP_COMMENTS_0000'n
WHERE MOTPComponentDescription = '4e (p&p): Growing and developing professionally';

在这里查看数据集的片段：QEofs0dhyZ23yC32ccbqg/edit?usp=sharing

结果: 1a (obs)：展示内容和教学知识: 700

1a (p&p)：展示内容和教育学知识: 606

1e (obs)：设计连贯指令: 15622

1e (p&p)：设计连贯指令: 1135

2a:创造尊重和和睦的环境: 2466

2d:管理学生行为: 1005

3b:使用提问和讨论技巧: 808

3c:让学生参与学习: 2516

3d:在教学中使用评估: 3058

4e (obs)：专业成长和发展: 5245

4e (p&p)：专业成长和发展: 588

和= 33746

33746 != 20599

寻找任何关于哪里出错的想法，或者是否有更好的方法来获得我想要的结果( MOTPCopmponentDescription对唯一的MOTPID的计数)。提前谢谢你！

Answer 1

要讨论StackOverflow上的SAS问题，SASHELP库中的示例数据非常有用。让我们使用汽车数据集。

标题“你认为问题是没有问题的”；

title2“计数一切制造”；

proc sql;
    select count (distinct Make) as distinct_makes from sashelp.cars;
quit;

• 给予38；

title2“数生产一定数量气缸的汽车的生产”；

proc sql;
    select 'n.a.' as Cylinders, count (distinct Make) as distinct_makes from sashelp.cars where Cylinders = . union
    select ' 3  ' as Cylinders, count (distinct Make) as distinct_makes from sashelp.cars where Cylinders = 3 union
    select ' 4  ' as Cylinders, count (distinct Make) as distinct_makes from sashelp.cars where Cylinders = 4 union
    select ' 5  ' as Cylinders, count (distinct Make) as distinct_makes from sashelp.cars where Cylinders = 5 union
    select ' 6  ' as Cylinders, count (distinct Make) as distinct_makes from sashelp.cars where Cylinders = 6 union
    select ' 8  ' as Cylinders, count (distinct Make) as distinct_makes from sashelp.cars where Cylinders = 8 union
    select '10  ' as Cylinders, count (distinct Make) as distinct_makes from sashelp.cars where Cylinders = 10 union
    select '12  ' as Cylinders, count (distinct Make) as distinct_makes from sashelp.cars where Cylinders = 12;
quit;

• 给出1台生产3缸，26台生产4缸等，“加法”达80多个；

title2“您可以手动验证这些清单中的结果”；

proc sql;
    select Cylinders, Make, Model from sashelp.cars order by Cylinders, Make;
    select Make, Cylinders, Model from sashelp.cars order by Make, Cylinders;
quit;

标题“你所称的解决方案是产生不可预测的结果”；

title2“如果输入是单向排序的，它就会产生这个结果”；

proc sort data=sashelp.cars out=cars_short2long;
    by length;
run;
proc sort data=cars_short2long nodupkey out=cars_short2long_clean dupout=dups;
    by Make;
run;
proc freq data=cars_short2long_clean;
    table Cylinders;
run;

• 表示没有人会制造10辆汽缸车；

title2“如果以另一种方式对输入进行排序，则会产生此结果”；

proc sort data=sashelp.cars out=cars_long2short;
    by descending length;
run;
proc sort data=cars_long2short nodupkey out=cars_long2short_clean dupout=dups;
    by Make;
run;
proc freq data=cars_long2short_clean;
    table Cylinders;
run;

• 表明没有人会制造3缸车；

Answer 2

下面是我想出的解决方案，得到了我正在寻找的确切结果：

data comment_analysis;
set WORK.'0__1_MOTP_COMMENTS_0001'n;
run;

proc sort data=comment_analysis nodupkey out=comment_analysis_clean dupout=dups;
by motpid;
run;

proc freq data=comment_analysis_clean;
table MOTPComponentDescription;
run;

这是我一直在寻找的输出: MOTPComponentDescription频率百分比

1a (obs)：展示内容和教育学知识520 2.52%

1a (p&p)：展示内容和教育学知识400 1.94%

1e (obs)：设计连贯指令11423 55.45%

1e (p&p)：设计连贯指令526 2.55%

2a:创造尊重和和睦的环境1629 7.91%

2d:管理学生行为556 2.70%

3b:使用提问和讨论技巧563 2.73%

3c:让学生参与学习1593 7.73%

3d:在教学中使用评估1818 8.83%

4e (obs)：专业成长和发展1235 6%

4e (p&p)：增长和发展专业336 1.64%