I need help with this:
Create a VIEW called ‘top_three’ that shows the stock levels for the top
three selling products (by volume, not value). The VIEW should contain the following
fields:
- name (from the product table)
- A field totalling the volume of sales called ‘number_of_sales’
- The four stock level fields: eu, us, uk and amea
(b) alter the script to add a field to the view for that shows the total sales as a
percentage of total stock. Call this field ‘sale_stock_percentage’
The tables were imported from excel so below are the tables:
product
product_id name unit_cost
222uuu laptop 1996.99
222vvv headphones 320.95
222www speakers 150.99
222xxx keyboard 85.00
222yyy mouse 55.00
Stock
product_id uk eu us amea
222uuu 219 80 150 353
222vvv 532 243 299 256
222www 34 90 15 148
222xxx 277 309 265 321
222yyy 56 17 54 234
Orders
product_id quantity total_gbp
222uuu 1 1996.99
222yyy 2 110.00
222vvv 7 2246.65
222www 4 603.96
222vvv 16 5135.20
222vvv 13 4172.35
222yyy 16 880.00
222xxx 10 850.00
222www 5 754.95
222www 1 150.99
The code I have written:
proc sql; create view top_three as select a.name, b.quantity, c.uk, c.eu, c.us, c.amea from work.product a full join work.stock c on a.product_id = c.product_id full join work.orders b on a.product_id = b.product_id ;
Thanks in advance
Start by making your code work; as it is, it causes a syntax error.
Sums are easily done in SQL:
sum(uk,us,eu,amea) as total_sales
You can then use PROC RANK to get the top three products. Or you can order the view by descending calculated total_sales, and use OUTOBS=3 on the PROC SQL statement.
From which total volume do you want to calculate the percentages? From all products, or just the top three?
@don_dadda wrote:
So i need the totals to be totals per row (product) so a column at the end totalling each row.
That's what my formula does.
The percentage will be a total of the quantity column from another table called orders and joined on the product_id. Would i just join all three tables to create the view first? I guess i need that table in there because it has the quantity of sales
In order to understand what you really want, please supply examples for the three tables involved by posting code (data steps with datalines) that create the example datasets. Also show the expected result from these.
Use the "little running man" next to the one indicated to post the codes:
Post data steps, no pictures, no Excel files.
The tables have been imported from excel, so below are examples of what they look like:
product
product_id name unit_cost
222uuu laptop 1996.99
222vvv headphones 320.95
222www speakers 150.99
222xxx keyboard 85.00
222yyy mouse 55.00
stock
product_id uk eu us amea
222uuu 219 80 150 353
222vvv 532 243 299 256
222www 34 90 15 148
222xxx 277 309 265 321
222yyy 56 17 54 234
orders
product_id quantity total_gbp
222uuu 1 1996.99
222yyy 2 110.00
222vvv 7 2246.65
222www 4 603.96
222uuu 5 9984.96
222vvv 16 5135.20
222vvv 13 4172.35
222yyy 16 880.00
222xxx 10 850.00
222www 5 754.95
222www 1 150.99
222www 1 150.99
222xxx 3 255.00
222uuu 2 3993.98
222vvv 17 5456.15
The actual question I have been asked is:
Create a VIEW called ‘top_three’ that shows the stock levels for the top
three selling products (by volume, not value). The VIEW should contain the following
fields:
- name (from the product table)
- A field totalling the volume of sales called ‘number_of_sales’
- The four stock level fields: eu, us, uk and amea
then
alter the script to add a field to the view for that shows the total sales as a
percentage of total stock. Call this field ‘sale_stock_percentage’
My code that I have at the moment:
proc sql; create view top_three as select a.name, b.quatity, c.uk, c.eu, c.us, c.amea from work.product a full join work.stock c on a.product_id = c.product_id full join work.order_product b on a.product_id = b.product_id ;
The tables were imported from excel so below are the tables:
product
product_id name unit_cost
222uuu laptop 1996.99
222vvv headphones 320.95
222www speakers 150.99
222xxx keyboard 85.00
222yyy mouse 55.00
Stock
product_id uk eu us amea
222uuu 219 80 150 353
222vvv 532 243 299 256
222www 34 90 15 148
222xxx 277 309 265 321
222yyy 56 17 54 234
Orders
product_id quantity total_gbp
222uuu 1 1996.99
222yyy 2 110.00
222vvv 7 2246.65
222www 4 603.96
222vvv 16 5135.20
222vvv 13 4172.35
222yyy 16 880.00
222xxx 10 850.00
222www 5 754.95
222www 1 150.99
The code I have written:
proc sql; create view top_three as select a.name, b.quantity, c.uk, c.eu, c.us, c.amea from work.product a full join work.stock c on a.product_id = c.product_id full join work.orders b on a.product_id = b.product_id ;
And this is the actual question I have been asked:
Create a VIEW called ‘top_three’ that shows the stock levels for the top
three selling products (by volume, not value). The VIEW should contain the following
fields:
- name (from the product table)
- A field totalling the volume of sales called ‘number_of_sales’
- The four stock level fields: eu, us, uk and amea
(b) alter the script to add a field to the view for that shows the total sales as a
percentage of total stock. Call this field ‘sale_stock_percentage’
Hello,
This should be close to what you want :
data work.product;
informat name $10.;
input product_id $ name $ unit_cost;
datalines;
222uuu laptop 1996.99
222vvv headphones 320.95
222www speakers 150.99
222xxx keyboard 85.00
222yyy mouse 55.00
;
run;
data work.Stock;
input product_id $ uk eu us amea;
datalines;
222uuu 219 80 150 353
222vvv 532 243 299 256
222www 34 90 15 148
222xxx 277 309 265 321
222yyy 56 17 54 234
;
run;
data work.Orders;
input product_id $ quantity total_gbp;
datalines;
222uuu 1 1996.99
222yyy 2 110.00
222vvv 7 2246.65
222www 4 603.96
222vvv 16 5135.20
222vvv 13 4172.35
222yyy 16 880.00
222xxx 10 850.00
222www 5 754.95
222www 1 150.99
;
run;
proc means data=work.Orders noprint nway;
class product_id;
var quantity;
output out=work.toplist(drop=_TYPE_ rename=(_freq_=NumberOrderLines)) sum= / autolabel autoname;
run;
proc sort data=work.toplist out=work.top3list;
by descending quantity_sum;
run;
PROC SQL noprint;
create view work.top_three as
select t1.product_id , t1.name
, t2.NumberOrderLines , t2.quantity_sum
, t3.uk , t3.eu , t3.us , t3.amea
, (t2.quantity_sum / SUM(t3.uk,t3.eu,t3.us,t3.amea)) as sale_stock_percentage
format = percent7.2
from work.product as t1
, work.top3list(obs=3) as t2
, work.Stock as t3
where t1.product_id = t2.product_id = t3.product_id
order by t2.quantity_sum desc
; QUIT;
/* end of program */
Koen
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.