SAS Query - Score Overriding query

[scb]

Can anyone help? Thanks in advance.

I have the following dataset:

1) Captain will override all the score of the team

2) Deputy Captain (A2) will override his own Region score (within the same team)

3) A3 will override A4 score within his region and city (within the same team)

DATA TEAM;
INPUT PLAYER $ TEAM_NAME $ SUBTEAM $ RANK $ REGION $ CITY $ SCORE;
DATALINES;
P1 SUPER CAPTAIN A1 . . .
P2 SUPER T1 A2 R1 . .
P3 SUPER T1 A3 R1 C1 .
P4 SUPER T1 A3 R1 C2 .
P5 SUPER T1 A4 R1 C1 20
P6 SUPER T1 A4 R1 C1 50
P7 SUPER T1 A4 R1 C1 50
P8 SUPER T1 A4 R1 C2 80
P9 SUPER T1 A4 R1 C2 70
P10 SUPER T1 A4 R1 C2 90
P2 SUPER T2 A2 R5 . .
P3 SUPER T2 A3 R5 C8 .
P4 SUPER T2 A3 R5 C9 .
P5 SUPER T2 A4 R5 C8 20
P6 SUPER T2 A4 R5 C8 50
P7 SUPER T2 A4 R5 C9 50
P8 SUPER T2 A4 R5 C9 80
P1 EAGLE CAPTAIN A1 . . .
P2 EAGLE T2 A3 R2 C3 .
P3 EAGLE T2 A3 R2 C4 .
P4 EAGLE T2 A4 R2 C3 20
P5 EAGLE T2 A4 R2 C3 70
P6 EAGLE T2 A4 R2 C3 40
P7 EAGLE T2 A4 R2 C4 60
P8 EAGLE T2 A4 R2 C4 50
P9 EAGLE T2 A4 R2 C4 80
P1 BULL CAPTAIN A1 . . .
P2 BULL T3 A2 R3 . .
P3 BULL T3 A3 R3 . 10
P4 BULL T3 A3 R3 . 80
P5 BULL T3 A3 R3 . 30
P6 BULL T3 A3 R3 . 60
P7 BULL T3 A3 R3 . 10
P8 BULL T3 A3 R3 . 60
P9 BULL T3 A3 R3 . 100
P2 BULL T4 A2 R4 . .
P3 BULL T4 A3 R4 . 20
P4 BULL T4 A3 R4 . 30
P5 BULL T4 A3 R4 . 40
P6 BULL T4 A3 R4 . 40
P7 BULL T4 A3 R4 . 50
P8 BULL T4 A3 R4 . 60
P9 BULL T4 A3 R4 . 80
;
run;

Desired result:

Player Team_Name  SubTeam     Rank  Region  City Score

P1    SUPER       CAPTAIN     A1    .   .  560

P2    SUPER       T1          A2    R1     360

P3    SUPER       T1          A3    R1 C1  120

P4    SUPER       T1          A3    R1 C2  240

P5    SUPER       T1          A4    R1 C1 20

P6    SUPER       T1          A4    R1 C1 50

P7    SUPER       T1          A4    R1 C1 50

P8    SUPER       T1          A4    R1 C2 80

P9    SUPER       T1          A4    R1 C2 70

P10   SUPER       T1          A4    R1 C2 90

P2    SUPER       T2          A2    R5       200

P3    SUPER       T2          A3    R5 C8    70

P4    SUPER       T2          A3    R5 C9    130

P5    SUPER       T2          A4    R5 C8 20

P6    SUPER       T2          A4    R5 C8 50

P7    SUPER       T2          A4    R5 C9 50

P8    SUPER       T2          A4    R5 C9 80

P1    EAGLE       CAPTAIN     A1             320

P2    EAGLE       T2          A3    R2 C3    130

P3    EAGLE       T2          A3    R2 C4    190

P4    EAGLE       T2          A4    R2 C3 20

P5    EAGLE       T2          A4    R2 C3 70

P6    EAGLE       T2          A4    R2 C3 40

P7    EAGLE       T2          A4    R2 C4 60

P8    EAGLE       T2          A4    R2 C4 50

P9    EAGLE       T2          A4    R2 C4 80

P1    BULL        CAPTAIN     A1          670  

P2    BULL       T3          A2    R3    350

P3    BULL       T3          A3    R3    10

P4    BULL       T3          A3    R3    80

P5    BULL       T3          A3    R3    30

P6    BULL       T3          A3    R3    60

P7    BULL        T3          A3    R3    10

P8    BULL        T3          A3    R3    60

P9    BULL        T3          A3    R3    100

P2    BULL        T4          A2    R4     320

P3    BULL        T4          A3    R4     20

P4    BULL        T4          A3    R4    30

P5    BULL        T4          A3    R4     40

P6    BULL        T4          A3    R4    40

P7    BULL        T4          A3    R4    50

P8    BULL        T4          A3    R4    60

P9    BULL        T4          A3    R4    80

[ChrisNZ]

Like this?

proc sql;
  select team.*
        ,coalesce(S1,S2,S3,SCORE) as NEWSCORE
  from TEAM                                        a
          left join
       (select TEAM_NAME, sum(SCORE) as S1 
        from TEAM group by 1)                      a1
          on a.RANK='A1' and a.TEAM_NAME=a1.TEAM_NAME
          left join
       (select TEAM_NAME, REGION, sum(SCORE) as S2 
        from TEAM group by 1,2)                    a2
          on a.RANK='A2' and a.TEAM_NAME=a2.TEAM_NAME and a.REGION=a2.REGION
          left join
       (select TEAM_NAME, REGION, CITY, sum(SCORE) as S3 
        from TEAM group by 1,2,3)                  a3
          on a.RANK='A3' and a.TEAM_NAME=a3.TEAM_NAME and a.REGION=a3.REGION and a.CITY=a3.CITY ;

[ChrisNZ · Accepted solution]

Probably a bit better:

proc sql;
  select team.*
        ,coalesce(S1,S2,S3,SCORE) as NEWSCORE
  from TEAM                                          a
          left join
       (select TEAM_NAME, sum(SCORE) as S1 
        from TEAM group by 1)                        a1
          on a.RANK='A1' and a.TEAM_NAME=a1.TEAM_NAME
          left join
       (select TEAM_NAME, REGION, sum(SCORE) as S2 
        from TEAM 
        where REGION ne ' '
        group by 1,2)                                a2
          on a.RANK='A2' and a.TEAM_NAME=a2.TEAM_NAME and a.REGION=a2.REGION
          left join
       (select TEAM_NAME, REGION, CITY, sum(SCORE) as S3 
        from TEAM 
        where REGION ne ' ' & CITY ne ' '
        group by 1,2,3)                              a3
          on a.RANK='A3' and a.TEAM_NAME=a3.TEAM_NAME and a.REGION=a3.REGION and a.CITY=a3.CITY 
  order by a.TEAM_NAME, a.PLAYER, a.RANK;

[ChrisNZ]

Is this an exercise as part of a course?