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

How can I perform an sql join using a dataset cretaed by the sql group-by statement. Example:

 

data sales;
	input id $1. period sale;
	cards;
A 1 100
A 2 200
B 1 150
B 2 250
	;
run;

data names;
	input id $1. name $7.;
	cards;
A nameA
B nameB
	;
run;

data want;
	input id $1. sum_sales name $7.;
	cards;
A 300 nameA
B 400 nameB
	;
run;

 

 

I've tried to use the following code but it doesn't work. I know I could first merge the datasets sales and names and then run proc sql (or proc means) to obtain sum, but I wonder what is the problem with the code below.

proc sql;

create table want as

(select id, sum(sale) as sum_sale

from sales

group by id) A

left join names B

on A.id=names.id;

quit;
1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

In your code the (outer) SELECT statement is missing.

 

Corrected version:

proc sql;
create table want as select a.*, b.name from
(select id, sum(sale) as sum_sales
 from sales
 group by id) A
left join names B
on A.id=names.id;
quit;

Here's an alternative solution without an inline view, i.e. with only one SELECT statement:

proc sql;
create table want as
select a.id, sum(sale) as sum_sales, name
from sales a, names b
where a.id=b.id
group by a.id, name;
quit;

View solution in original post

2 REPLIES 2
FreelanceReinh
Jade | Level 19

In your code the (outer) SELECT statement is missing.

 

Corrected version:

proc sql;
create table want as select a.*, b.name from
(select id, sum(sale) as sum_sales
 from sales
 group by id) A
left join names B
on A.id=names.id;
quit;

Here's an alternative solution without an inline view, i.e. with only one SELECT statement:

proc sql;
create table want as
select a.id, sum(sale) as sum_sales, name
from sales a, names b
where a.id=b.id
group by a.id, name;
quit;
chris2377
Quartz | Level 8
Thanks a lot. Now it's clear

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

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

Browse our catalog!

Discussion stats
  • 2 replies
  • 9831 views
  • 1 like
  • 2 in conversation