topic Re: Proc SQL Query in SAS Procedures

Proc SQL Query

scb — Sat, 02 May 2020 12:03:51 GMT

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

Re: Proc SQL Query

ed_sas_member — Sat, 02 May 2020 13:10:22 GMT

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;

Re: Proc SQL Query

Kurt_Bremser — Sat, 02 May 2020 13:04:47 GMT

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;