SAS Query

[scb]

DATA have;                                                                                                                              

  INPUT ID $ TeamName $ Team $ Rank $ Point;                                                                                           

  DATALINES;                                                                                                                            

111 Super Head A1 0                                                                                                                  

112 Super T2 A2 0                                                                                                                       

114 Super T2 A3 800                                                                                                                    

115 Super T2 A3 100                                                                                                                     

116 Super T2 A3 200                                                                                                                    

112 Super T3 A2 0                                                                                                                       

114 Super T3 A3 400                                                                                                                    

115 Super T3 A3 200                                                                                                                     

116 Super T3 A3 200                                                                                                                    

117 Super T3 A3 800        

111 Tiger Head A1 0                                                                                                                  

112 Tiger T2 A2 0                                                                                                                       

114 Tiger T2 A3 800                                                                                                                    

115 Tiger T2 A3 100                                                                                                                     

116 Tiger T2 A3 300                                                                                                                    

112 Tiger T3 A2 0                                                                                                                       

114 Tiger T3 A3 400                                                                                                                    

115 Tiger T3 A3 200                                                                                                                     

116 Tiger T3 A3 200                                                                                                                    

117 Tiger T3 A3 1000                                                                                                                     

 ;

run;

Desired Output:

ID  TeamName Team     Rank  Ponts  TPoints  Person_inherited

111 Super      Head      Head     0         2700      7                                                                                                        

112 Super     T2         A2     0         1100      3                                                                                                             

114 Super     T2        A3   800        800       0                                                                                                           

115 Super     T2       A3     100        100       0                                                                                                            

116 Super     T2       A3     200        200       0                                                                                                           

112 Super     T3       A2     0        1600        4                                                                                                            

114 Super     T3       A3   400      400         0                                                                                                            

115 Super     T3     A3    200      200          0                                                                                                            

116 Super     T3     A3     200     200         0                                                                                                           

117 Super    T3      A3   800     800           0

111 Tiger     Head  Head   0      2800          6                                                                                                        

112 Tiger     T2     A2   0        1200            3                                                                                                             

114 Tiger     T2     A3   800    800             0                                                                                                           

115 Tiger     T2     A3   100    100            0                                                                                                            

116 Tiger     T2     A3   300    300            0                                                                                                           

112 Tiger     T3     A2   0      1600           3                                                                                                            

114 Tiger     T3     A3   400     400         0                                                                                                            

116 Tiger     T3     A3   200     200         0                                                                                                           

117 Tiger    T3      A3   1000    1000        0

  May I know how to get the TPoints and person inherited? Rank A2 Tpoints will be from all A3 within the team.  A2 T Points will  be from A3 within the team; A3 Tpoints will be his own pts.

Head will get the accumulated points from Rank A2 within his team.

Person inherited for A3 will be 0, A2 will be the total A3 ID, while Head will get the accumulated person inherited from Rank A2 within his team.

Anyone can help? thanks.

[art297 · Accepted solution]

I think the reason that no one responded is because you didn't provide enough information.

Regardless, I was bored this afternoon, so decided to try and solve WHAT I THINK is the problem you are trying to solve.

Take a look at the following and let me know if it does what you want. The result doesn't meet your specs because, for whatever reason, you droped "115 Tiger T3 A3 200 " from your desired output:

DATA have;                                                                                                                              
  INPUT ID $ TeamName $ Team $ Rank $ Point;                                                                                           
  DATALINES;                                                                                                                            
111 Super Head A1 0
112 Super T2 A2 0
114 Super T2 A3 800
115 Super T2 A3 100
116 Super T2 A3 200
112 Super T3 A2 0
114 Super T3 A3 400
115 Super T3 A3 200
116 Super T3 A3 200
117 Super T3 A3 800
111 Tiger Head A1 0
112 Tiger T2 A2 0
114 Tiger T2 A3 800
115 Tiger T2 A3 100
116 Tiger T2 A3 300
112 Tiger T3 A2 0
114 Tiger T3 A3 400
115 Tiger T3 A3 200
116 Tiger T3 A3 200
117 Tiger T3 A3 1000                                                                                                                     
;
run;
data need;
  do until (last.TeamName);
    set have;
    by TeamName;
    if first.TeamName then do;
      tpoints=0;
      Person_inherited=0;
    end;
    else do;
      tpoints+point;
      if point gt 0 then Person_inherited+1;
    end;
  end;
  do until (last.TeamName);
    set have;
    by TeamName Team;
    if first.TeamName then output;
    else do;
      tpoints=0;
      Person_inherited=0;
      output;
    end;
  end;
run;
data want (drop=_:);
  do until (last.Team);
    set need (rename=(tpoints=_tpoints Person_inherited=_Person_inherited));
    by TeamName Team;
    if Team eq 'Head' then do;
      tpoints=_tpoints;
      Person_inherited=_Person_inherited;
    end;
    else do;
      if first.Team then do;
        tpoints=0;
        Person_inherited=0;
      end;
      else do;
        tpoints+point;
        if point gt 0 then Person_inherited+1;
      end;
    end;
  end;
  do until (last.Team);
    set need (rename=(tpoints=_tpoints Person_inherited=_Person_inherited));
    by TeamName Team;
    if first.Team then output;
    else do;
      tpoints=0;
      Person_inherited=0;
      output;
    end;
  end;
run;

Art, CEO, AnalystFinder.com