SAS Query - Score Overriding query

scb — Wed, 04 Nov 2020 16:14:28 GMT

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

Re: SAS Query - Score Overriding query

ChrisNZ — Thu, 05 Nov 2020 04:23:32 GMT

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 ;

Re: SAS Query - Score Overriding query

ChrisNZ — Thu, 05 Nov 2020 04:40:02 GMT

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;

Re: SAS Query - Score Overriding query

ChrisNZ — Thu, 05 Nov 2020 07:16:13 GMT

Is this an exercise as part of a course?

topic Re: SAS Query - Score Overriding query in SAS Programming

SAS Query - Score Overriding query

Re: SAS Query - Score Overriding query

Re: SAS Query - Score Overriding query

Re: SAS Query - Score Overriding query