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;
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.