topic Re: The art of SUBQUERY in SAS Procedures

The art of SUBQUERY

Riversus — Thu, 13 Oct 2016 16:31:41 GMT

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:

var1
mean(var2)
sum(var3) only where var2>=1
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.

Re: The art of SUBQUERY

PGStats — Thu, 13 Oct 2016 18:13:08 GMT

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;

Re: The art of SUBQUERY

ChrisHemedinger — Thu, 13 Oct 2016 18:25:15 GMT

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)

Re: The art of SUBQUERY

Cynthia_sas — Thu, 13 Oct 2016 18:38:39 GMT

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

Re: The art of SUBQUERY

PGStats — Thu, 13 Oct 2016 19:17:29 GMT

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;

Re: The art of SUBQUERY

Ksharp — Fri, 14 Oct 2016 06:13:27 GMT

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;

Re: The art of SUBQUERY

Riversus — Fri, 14 Oct 2016 07:32:55 GMT

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?

Re: The art of SUBQUERY

Riversus — Fri, 14 Oct 2016 07:34:16 GMT

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

Re: The art of SUBQUERY

NewsGuy — Fri, 14 Oct 2016 16:17:12 GMT

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

Re: The art of SUBQUERY

ChrisHemedinger — Fri, 14 Oct 2016 16:30:57 GMT

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).

Re: The art of SUBQUERY

NewsGuy — Fri, 14 Oct 2016 16:32:57 GMT

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