CODEWITHSUNDEEP
sasanalytics
Ace_sas
Ace_sas

Reputation: 3

Getting next observation per group

I am working on a dataset in SAS to get the next observation's score should be the current observation's value for the column Next_Row_score. If there is no next observation then the current observation's value for the column Next_Row_score should be 'null'per group(ID). For better illustration i have provided the sample below dataset : 

ID Score 
10 1000 
10 1500 
10 2000 
20 3000 
20 4000 
30 2500

Resultant output should be like - 

ID Salary Next_Row_Salary 
10 1000   1500 
10 1500   2000 
10 2000   . 
20 3000   4000 
20 4000   .  
30 2500   2500

Thank you in advance for your help.

Upvotes: 0

Views: 239

Answers (3)

Sean
Sean

Reputation: 1120

There is a simpler (in my mind, at least) proc sql approach that doesn't involve loops:

data have;
  input ID Score;
  datalines;
10 1000 
10 1500 
10 2000 
20 3000 
20 4000 
30 2500 
;
run;

/*count each observation's place in its ID group*/
data have2;
    set have;
    count + 1;
    by id;
    if first.id then count = 1;
run;

/*if there is only one ID in a group, keep original score, else lag by 1*/
proc sql;
    create table want as select distinct
        a.id, a.score,
        case when max(a.count) = 1 then a.score else b.score end as score2
        from have2 as a
        left join have2 (where = (count > 1)) as b
        on a.id = b.id and a.count = b.count - 1
        group by a.id;
quit;

Upvotes: 0

D. O.
D. O.

Reputation: 626

Try this :

data have;
  input ID Score;

  datalines;
10 1000 
10 1500 
10 2000 
20 3000 
20 4000 
30 2500 
;
run;

proc sql noprint;
    select count(*)  into :obsHave
    from have;
quit;

data want2(rename=(id1=ID Score1=Salary) drop=ID id2 Score);
    do i=1 to &obsHave;
        set have point=i;
        id1=ID;
        Score1=Score;
        j=i+1;
        set have point=j;
        id2=ID;
        if id1=id2 then do;
            Next_Row_Salary = Score;
        end;
        else Next_Row_Salary=".";
        output;
    end;
stop;
;
run;

Upvotes: 0

Shenglin Chen
Shenglin Chen

Reputation: 4554

data want(drop=_: flag);
   merge have have(firstobs=2 rename=(ID=_ID Score=_Score));
   if ID=_ID then do;
       Next_Row_Salary=_Score;
       flag+1;
   end;
   else if ID^=_ID and flag>=1 then do;
           Next_Row_Salary=.;
           flag=.;
   end;
   else Next_Row_Salary=score;
run;

Upvotes: 1

Related Questions