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
... View more