Obsidian | Level 7

## Proc SQL Query

May I know how to calculate the sum for team1 with finish le 3 for team1, and finish le 5 for team2 (as stated in below code) for both temp_cl columns?

DATA TEST;
INPUT ID TEAM \$ RANK \$ CL1 CL2 FINISH;
DATALINES;
120 T1 A1 0 0 1
121 T1 A2 20 30 1
122 T1 A2 30 50 2
123 T1 A2 40 60 3
124 T1 A2 50 40 4
125 T1 A2 60 30 5
220 T2 A1 0 0 1
221 T2 A2 24 130 1
222 T2 A2 34 150 2
223 T2 A2 44 160 3
224 T2 A2 54 140 4
225 T2 A2 64 130 5
;
RUN;

PROC SQL;
CREATE TABLE TEST1 AS SELECT *,
COALESCE((SELECT SUM(CL1) FROM TEST WHERE TEAM=A.TEAM AND FINISH LE 5),CL1)
AS TEMP_CL1,
COALESCE((SELECT SUM(CL2) FROM TEST WHERE TEAM=A.TEAM AND FINISH LE 5),CL2)
AS TEMP_CL2
FROM TEST A;
QUIT

2 REPLIES 2
Meteorite | Level 14

## Re: Proc SQL Query

Hi @scb

Does this code meet your expectations?

Best,

``````proc sql;
create table test1 as

select a.*, b.TEMP_CL1, b.TEMP_CL2
from test as a inner join
(select distinct team,
sum(CL1) as TEMP_CL1,
sum(CL2) as TEMP_CL2
from test
where team="T1" and finish le 2) as b
on a.team=b.team

union all corr

select a.*, b.TEMP_CL1, b.TEMP_CL2
from test as a inner join
(select distinct team,
sum(CL1) as TEMP_CL1,
sum(CL2) as TEMP_CL2
from test
where team="T2" and finish le 5) as b
on a.team=b.team;
run;``````

Super User

## Re: Proc SQL Query

Please stop shouting at the poor SAS interpreter, it hasn't done anything to deserve this.

Try this:

``````proc sql;
create table want as
select
team,
sum (case
when team = 'T1' and finish le 3 or team = 'T2' and finish le 5
then cl1
else 0
end
) as temp_cl1,
sum (case
when team = 'T1' and finish le 3 or team = 'T2' and finish le 5
then cl2
else 0
end
) as temp_cl2
from have
group by team
;
quit;``````
Discussion stats
• 2 replies
• 323 views
• 0 likes
• 3 in conversation