topic Re: Using commas between intermediate tables in SAS Programming

Using commas between intermediate tables

mansour_ib_sas — Mon, 27 Aug 2018 12:38:51 GMT

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

Re: Using commas between intermediate tables

Ksharp — Mon, 27 Aug 2018 13:13:24 GMT

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;

Re: Using commas between intermediate tables

mansour_ib_sas — Mon, 27 Aug 2018 13:35:57 GMT

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)

Re: Using commas between intermediate tables

mansour_ib_sas — Mon, 27 Aug 2018 13:58:58 GMT

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