topic SQL group by + join in SAS Procedures

SQL group by + join

chris2377 — Fri, 08 Jan 2016 12:07:59 GMT

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;

Re: SQL group by + join

FreelanceReinh — Fri, 08 Jan 2016 12:34:29 GMT

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;

Re: SQL group by + join

chris2377 — Fri, 08 Jan 2016 12:53:39 GMT

Thanks a lot. Now it's clear