topic Re: Need help with views and select statements in New SAS User

Need help with views and select statements

don_dadda — Sun, 31 Oct 2021 09:43:58 GMT

I have three tables, columns in brackets:
Orders (product_id quantity)
Products (product_id name)
Stock (product_id uk us eu amea)

These have been imported into SAS Studio and I now need to 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 1. (a) that shows the total sales as a
percentage of total stock. Call this field ‘sale_stock_percentage’

this is the code I have so far:

create view top_three_stock 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
;

Re: Need help with views and select statements

PaigeMiller — Sun, 31 Oct 2021 10:36:24 GMT

Explain what formula would be used for SALE_STOCK_PERCENTAGE. Show us what you have tried for SALE_STOCK_PERCENTAGE.

Re: Need help with views and select statements

don_dadda — Sun, 31 Oct 2021 10:42:55 GMT

Hi, I would need to find the total of all quantity and divide this by the total of all the 4 stock columns to get the percentage

Re: Need help with views and select statements

PaigeMiller — Sun, 31 Oct 2021 10:50:09 GMT

Show us what you have tried for SALE_STOCK_PERCENTAGE.

Re: Need help with views and select statements

don_dadda — Sun, 31 Oct 2021 10:58:20 GMT

I havent tried anything as of yet, here is where I have got to:

create table join1 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.order_product b
on a.product_id = b.product_id
;

create table sort1 as
select distinct name, sum(quantity) as number_of_sales, uk, us, eu, amea
from join1
group by name
order by number_of_sales desc
;

proc sql outlobs=3;
create view top_three as
select name, number_of_sales
from sort1;

my last piece isnt working as I would expect, it is listing all rows rather than just the top 3.

I do not know how to alter the view.

Re: Need help with views and select statements

PaigeMiller — Sun, 31 Oct 2021 11:50:07 GMT

proc sql outlobs=3;

Your log should show an error on this command. That's what you need to fix.