Hello,
Could you, please, explain to me in terms of join the meaning of the commas between intermediate tables (union for example ??).
Here is an example:
from the test table, I want to sum the var3 when the value is greater than 100 and the average of var4 when it is greater than 40.
I want to count the number of elements
the want result is:
count = 6
sum var3 500.
data test;
format var1 $2. var2 $2. var3 var4 var5;
input var1 var2 var3 var4 var5;
cards;
a1 b1 100 12 15
a2 b5 200 25 30
a2 b2 50 20 14
a3 b1 54 85 96
a3 b3 300 400 12
a3 b4 1 2 3
;
run;
proc sql;
create table test1 as select t.sum_var3,t1.moy_var4, t2.nb from
(
select sum(var3) as sum_var3 from test where var3 >100 ) as t
,
(select avg(var4) as moy_var4 from test where var4>40 ) as t1
,
(select count(*) as nb from test) t2
;quit;
Thank you
I think I understood, it is a Cartisian product between the intermediate tables.
In the program in my first message; each table (t, t1, t2) returns a one selection. the final result is 1 line
This example illustrates this:
we have 12 lines
data test;
format var1 $2. var2 $2. var3 var4 var5;
input var1 var2 var3 var4 var5;
cards;
a1 b1 100 12 15
a2 b5 200 25 30
a2 b2 50 20 14
a3 b1 54 85 96
a3 b3 300 400 12
a3 b4 1 2 3
;
run;
proc sql;
create table test1 as select t.var33,test.* from
(
select var3 as var33 from test where var3 >100 ) as t
,test
;quit;
Thank you
data test;
format var1 $2. var2 $2. var3 var4 var5;
input var1 var2 var3 var4 var5;
cards;
a1 b1 100 12 15
a2 b5 200 25 30
a2 b2 50 20 14
a3 b1 54 85 96
a3 b3 300 400 12
a3 b4 1 2 3
;
run;
proc sql;
create table test1 as
select
(select sum(var3) from test where var3 >100 ) as sum_var3
,
(select avg(var4) from test where var4>40 ) as moy_var4
,
(select count(*) from test) as nb
from test(obs=1)
;quit;
I have this error
88 proc sql;
89 create table test1 as
90 select
91 (select sum(var3) from test where var3 >100 ) as sum_var3
92 ,
___
78
76
ERROR 78-322: Expecting a ','.
ERROR 76-322: Syntax error, statement will be ignored.
93 (select avg(var4) from test where var4>40 ) as moy_var4
94 ,
95 (select count(*) from test) as nb
96 from test(obs=1)
I think I understood, it is a Cartisian product between the intermediate tables.
In the program in my first message; each table (t, t1, t2) returns a one selection. the final result is 1 line
This example illustrates this:
we have 12 lines
data test;
format var1 $2. var2 $2. var3 var4 var5;
input var1 var2 var3 var4 var5;
cards;
a1 b1 100 12 15
a2 b5 200 25 30
a2 b2 50 20 14
a3 b1 54 85 96
a3 b3 300 400 12
a3 b4 1 2 3
;
run;
proc sql;
create table test1 as select t.var33,test.* from
(
select var3 as var33 from test where var3 >100 ) as t
,test
;quit;
Thank you
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.