BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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

1 ACCEPTED SOLUTION

Accepted Solutions
mansour_ib_sas
Pyrite | Level 9

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

View solution in original post

3 REPLIES 3
Ksharp
Super User
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;
mansour_ib_sas
Pyrite | Level 9

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)
mansour_ib_sas
Pyrite | Level 9

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

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1200 views
  • 1 like
  • 2 in conversation