Proc SQL datamanipulation example

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 90
Accepted Solution

Proc SQL datamanipulation example

[ Edited ]

 

I have this table that have the volume for each product:

 

PRODUCTVOLUME
NK200
CN180
CY220

 

And another table the have the percentage share for each country:

 

REGIONSHARE
AUSTRALIA0.45
CHINA0.55

 

 

I want this output, the propotion the volume of each product by each country

 

PRODUCTREGIONVOLUME
NKAUSTRALIA90
NKCHINA110
CNAUSTRALIA81
CNCHINA99
CYAUSTRALIA99
CYCHINA121

 

 

How do I achieve this in PROC SQL? I have simplified the table, in real there are more columns.

 


Accepted Solutions
Solution
‎03-14-2016 09:53 PM
Grand Advisor
Posts: 17,383

Re: Proc SQL datamanipulation example

You're looking for a cross join or natural join.

You can use a.*, b.* to specify keeping all columns in each table. 

 

proc sql;
create table want as
select a.*, b.*, a.volume*b.share as volume_proportion
from table1 as a
CROSS JOIN table2 as b
order by a.product, b.region;
quit;

View solution in original post


All Replies
Solution
‎03-14-2016 09:53 PM
Grand Advisor
Posts: 17,383

Re: Proc SQL datamanipulation example

You're looking for a cross join or natural join.

You can use a.*, b.* to specify keeping all columns in each table. 

 

proc sql;
create table want as
select a.*, b.*, a.volume*b.share as volume_proportion
from table1 as a
CROSS JOIN table2 as b
order by a.product, b.region;
quit;
☑ This topic is SOLVED.

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

Discussion stats
  • 1 reply
  • 193 views
  • 0 likes
  • 2 in conversation