sas中如何对记录进行合并

shiyiming

例如有student表包含id、subject、score三个字段，现在要生成一张新表s1包含id、totalscore两个字段，其中totalscore为学生各科成绩的求和，在sas中这种操作应该如何进行。又假如要对id、subject均相同的记录进行合并（假设每个科目可能进行了不止一次考试），则又该如何进行，请各位高手不吝赐教，谢谢！

shiyiming

proc sort data=tmp;
by id;

data out(keep=id totalscore);
set tmp;
by id;
if first.id then totalscore=0;
totalscore+subject;
if last.id then output;
run;

至于合并
proc sort data=tmp;
by id subject;

data tmp1(drop=score rename=(subscore=score));
set tmp;
by id subject;
if first.subject then subscore=0;
subscore+score;
if last.subject then output;
run;

shiyiming

proc summary data=score;
by id;
var score;
output out=totscore sum=totscore;
run;

proc summary data=score;
by id subject;
var score;
output out=subjectscore sum=subjectscore;
run;

proc sql;
create table totscore as select id, sum(score) as totscore from score
group by id;
create table subjectscore as select id, subject, sum(score) as subjectscore from score
group by id, subject;

shiyiming

本来想偷懒不顶了，后来看了别的论坛一个斑竹的帖子提到提问的人都是不劳而获，应该回贴表示感谢，同时也是对别人劳动成果的尊重，觉得很有道理。
多谢各位高手的讲解 <!-- s:lol: --><img src="{SMILIES_PATH}/icon_lol.gif" alt=":lol:" title="Laughing" /><!-- s:lol: -->

shiyiming

[quote=&quot;yooyork&quot;:fd37b]proc summary data=score;
by id;
var score;
output out=totscore sum=totscore;
run;

proc summary data=score;
by id subject;
var score;
output out=subjectscore sum=subjectscore;
run;

proc sql;
create table totscore as select id, sum(score) as totscore from score
group by id;
create table subjectscore as select id, subject, sum(score) as subjectscore from score
group by id, subject;[/quote:fd37b]

其实一个proc summary过程就可以算出：
proc summary data=score;
class id subject;
types id id*subject;
output out=totscore sum=score;
run;

结果集totscore中_type_=2的是根据id的总分，_type_=3的是根据id和subject的科目总分。

提示：一个过程算出多种汇总层次的结果是SAS Proc Means/Summary的优势之一，特别是它具有优化的汇总顺序
首先汇总id+subject,然后根据id+subject的结果去汇总id,而不是直接汇总id,所以效率很高。_type_是区分汇总层次的变量，对于结果拆分很有用。
而数据库系的SQL到了SQL 99 才有类似的cube语句出现。

shiyiming

"totalscore"，字段名可以超过8个字符吗？

请教，用下面FREQ 过程可以吗？
/*--- create new dataset student2 --------*/
proc freq data=student noprint;
tables id/out=student2(rename=(count=totscore) drop=percent);
  weight score;
run;

/*- list student2 ------------------------*/
proc print data=student2;
run;
/*----------------------------------------*/

shiyiming

- "totalscore"，字段名可以超过8个字符吗？
8版本及以后版本的SAS变量名都支持到32个字符长。

freq对本例当然可以。看你习惯吧，用不同的tables / out=也可以一次输出多个汇总层次信息。