Help using Base SAS procedures

SQL group by + join

Accepted Solution Solved
Reply
Contributor
Posts: 50
Accepted Solution

SQL group by + join

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;

Accepted Solutions
Solution
‎01-08-2016 07:53 AM
Trusted Advisor
Posts: 1,115

Re: SQL group by + join

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


All Replies
Solution
‎01-08-2016 07:53 AM
Trusted Advisor
Posts: 1,115

Re: SQL group by + join

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;
Contributor
Posts: 50

Re: SQL group by + join

Thanks a lot. Now it's clear
☑ This topic is SOLVED.

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

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