sas上按条件和ID的变量累加和

Question

只要另一个变量保持不变，我就会尝试对一个变量求和。只要a是常数，我就想要累加和。当a更改时，sum重新启动。当有新的id时，sum重新开始。

enter image description here

我想这样做：enter image description here

谢谢

Answer 1

可以使用BY语句指定变量，这些变量的不同值组合将数据行组织到组中。您将在每组开始时重置一个累加值，并将其添加到组中每行的累加器中。使用retain在数据步长隐式循环迭代之间维护新的变量值。SUM语句是一个用于累积和保留的独特SAS功能。

示例：

data want;
  set have;
  by id a;
  if first.a then mysum = 0;
  mysum + dur;
run;

SUM语句与SUM函数不同。

<variable> + <expression>;  * SUM statement, unique to SAS (not found in other languages);

可以被认为是

retain <variable>;
<variable> = sum (<variable>, <expression>);

Answer 2

就我而言，你需要自连接你的表和一个排名的列。

它应该按id和a列进行排名。

FROM WORK.QUERY_FOR_STCKOVRFLW t1;是您在截图中提供的表

PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_STCKOVRFLW_0001 AS 
   SELECT t1.id, 
          t1.a, 
          t1.dur, 
          /* mono */
            (monotonic()) AS mono
      FROM WORK.QUERY_FOR_STCKOVRFLW t1;
QUIT;

PROC SORT
    DATA=WORK.QUERY_FOR_STCKOVRFLW_0001
    OUT=WORK.SORTTempTableSorted
    ;
    BY id a;
RUN;
PROC RANK DATA = WORK.SORTTempTableSorted
    TIES=MEAN
    OUT=WORK.RANKRanked(LABEL="Rank Analysis for WORK.QUERY_FOR_STCKOVRFLW_0001");
    BY id a;
    VAR mono;
RANKS rank_mono ;
RUN; QUIT;



PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_RANKRANKED AS 
   SELECT t1.id, 
          t1.a, 
          t1.dur, 
          /* SUM_of_dur */
            (SUM(t2.dur)) FORMAT=BEST12. AS SUM_of_dur
      FROM WORK.RANKRANKED t1
           LEFT JOIN WORK.RANKRANKED t2 ON (t1.id = t2.id) AND (t1.a = t2.a AND (t1.rank_mono  >=  t2.rank_mono ))
      GROUP BY t1.id,
               t1.a,
               t1.dur;
QUIT;