The art of SUBQUERY

[Riversus]

proc sql;
create table dataset2 as
select var1,

mean(var2) as average,

(select sum(var3) from dataset1 where var2>=1 group by var1),

(select sum(var3) from dataset1 where var2>=1 group by var1)
from dataset1
group by var1;
quit;

ERROR: SUBQUERY EVALUATED TO MORE THAN ONE ROW

Hi everyone,

I am new to SAS and I'd like to understand about subqueries. So what I want is to have a table with 4 columns:

1. var1
2. mean(var2)
3. sum(var3) only where var2>=1
4. sum(var3) only where var2<1

Therefore I want the "where" statement to trigger only for the last two columns. The subquery works if I dont add the "group by" but in that case I am not able to have the breakdown by var1. On the other hand if I add the "group by" in the subquery I get this error: Subquery evaluated to more than one row.

I know that I could obtain the same table with joins but I really would like to understand better how to exploit subqueries. 

Thanks in advance for your help.

[PGStats]

Since you want Var1 to be the same in the main query and the subquery, you must explicitly correlate the subqueries with the main query. Try:

proc sql;
create table dataset2 as
select var1,
	mean(var2) as average,
	(select sum(var3) from dataset1 where var1=A.var1 and var2>=1 group by var1) as var2Sup1,
	(select sum(var3) from dataset1 where var1=A.var1 and  var2<1 group by var1) as var2Inf1
from dataset1 as A
group by var1;
quit;

[PGStats · Accepted solution]

I should add that no subquery or join is needed to perform that operation. You can simply do:

proc sql;
create table dataset2 as
select var1,
	mean(var2) as average,
	sum(var3 * (var2>=1)) as var2Sup1,
	sum(var3 * (var2<1)) as var2Inf1
from dataset1
group by var1;
quit;

[Riversus]

I chose this answer as solution but I'd like to understand how the formula exactly work.

sum(var3 * (var2>=1)) as var2Sup1

it sums var3 by var1 as I want but I dont understand how the multiplication works. Is (var2>=1) a dummy variable? 

[ChrisHemedinger]

For an overview of the types of applications that subqueries support, see this topic about subqueries in SAS Enterprise Guide.  If you have SAS Enterprise Guide you can use point-and-click methods to generate some syntax you might learn from.  BUT, the SAS Enterprise Guide methods don't support correlated subqueries -- which @PGStats just helped you with.

In EG, here are the types of subqueries that you can create using query-based templates:

• Subqueries that return a single value
• Subqueries that return multiple values (multiple rows of a single field)
• Subqueries that appear as part of a filter of the raw data (on the WHERE clause)
• Subqueries that appear as part of a filter on the grouped data (on the HAVING clause)
• Subqueries that appear as part of a recode condition as part of recoding a column in a computed column (on the SELECT clause)

Here are the types of subqueries that you cannot create using query-based templates:

• Subqueries that form a derived table (subqueries that appear on the FROM clause)
• Subqueries that refer to columns on the outer query (correlated subqueries)

[Cynthia_sas]

To build on what Chris explained, in our SQL1 class, we show the types of subqueries that you can write in PROC SQL code and we also explain when/how to write non-correlated subqueries and use in-line views and have a discussion of correlated subqueries:
https://support.sas.com/edu/schedules.html?ctry=us&crs=SQL1#s1=3

cynthia

[Ksharp]

proc sql;
create table dataset2 as
select var1,

mean(var2) as average,

(select sum(var3) from dataset1 where var2>=1 and var1=a.var1),

(select sum(var3) from dataset1 where var2>=1 and var1=a.var1)
from dataset1 as a
group by var1;
quit;

[Riversus]

Also this code is good. Thank you 🙂 I dont know how to select more than a solution to the post

[NewsGuy]

Unless I misunderstand the data structure, I think the way I would do this is:

proc sql;
create table dataset2 as
select var1,

mean(var2) as average,

sum( case when var2>=1 then var3 else 0 end) as total1,

sum(case when var2<1 then var3 else 0 end) as total2
from dataset1
group by var1;
quit;

Dan Keating

[ChrisHemedinger]

Hey @NewsGuy, I think your approach is the same as @PGStats, except that you used a CASE instead of relying on the fact that an equality check (var2>=1) will resolve to a 1 or a 0, depending on the value of var2.  Your approach is more explicit, while @PGStats approach is more elegant (even if it takes a moment to think it through).

[NewsGuy]

I completely agree that it is the same and it is not a sub-query, so if the goal was to learn subqueries, it doesn't help.
I love the more elegant way and will try to use it, as well. But I offered mine up since it's something I use constantly and thought people might like it.

Dan