topic Re: Need help with a view, a total and an alter in SAS Programming

Need help with a view, a total and an alter

don_dadda — Sun, 31 Oct 2021 09:16:15 GMT

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

Re: Need help with a view, a total and an alter

Kurt_Bremser — Sun, 31 Oct 2021 08:09:07 GMT

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?

Re: Need help with a view, a total and an alter

don_dadda — Sun, 31 Oct 2021 08:17:02 GMT

So i need the totals to be totals per row (product) so a column at the end totalling each row.

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

Re: Need help with a view, a total and an alter

Kurt_Bremser — Sun, 31 Oct 2021 08:25:28 GMT

@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.

Re: Need help with a view, a total and an alter

don_dadda — Sun, 31 Oct 2021 08:39:05 GMT

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:

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
;

Re: Need help with a view, a total and an alter

don_dadda — Sun, 31 Oct 2021 09:06:37 GMT

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:

(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’

Re: Need help with a view, a total and an alter

sbxkoenk — Sun, 31 Oct 2021 19:22:35 GMT

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