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

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

10 REPLIES 10
PGStats
Opal | Level 21

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
PGStats
Opal | Level 21

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
Riversus
Fluorite | Level 6

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
Community Manager

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)
It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
Cynthia_sas
SAS Super FREQ
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
Super User

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
Fluorite | Level 6

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

NewsGuy
Fluorite | Level 6

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
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
NewsGuy
Fluorite | Level 6
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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