Help using Base SAS procedures

The art of SUBQUERY

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

The art of SUBQUERY

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.

 


Accepted Solutions
Solution
‎10-14-2016 03:18 AM
Respected Advisor
Posts: 4,922

Re: The art of SUBQUERY

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;
PG

View solution in original post


All Replies
Respected Advisor
Posts: 4,922

Re: The art of SUBQUERY

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;
PG
Solution
‎10-14-2016 03:18 AM
Respected Advisor
Posts: 4,922

Re: The art of SUBQUERY

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;
PG
New Contributor
Posts: 3

Re: The art of SUBQUERY

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? 

Community Manager
Posts: 2,954

Re: The art of SUBQUERY

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)
SAS Super FREQ
Posts: 8,866

Re: The art of SUBQUERY

Posted in reply to ChrisHemedinger
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
Super User
Posts: 10,028

Re: The art of SUBQUERY

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;

New Contributor
Posts: 3

Re: The art of SUBQUERY

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

New Contributor
Posts: 2

Re: The art of SUBQUERY

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

Community Manager
Posts: 2,954

Re: The art of SUBQUERY

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

New Contributor
Posts: 2

Re: The art of SUBQUERY

Posted in reply to ChrisHemedinger
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
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 627 views
  • 6 likes
  • 6 in conversation