# Untitled

Jan 19th, 2020
2,514
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
1. libname n 'C:\Users\student\Desktop\NorthWind';
2.
3. data daty;
4.     a="10.01.2019";
5. run;
6.
7. proc sql;
8. create table daty_1 as
9.     select *,
10.         input(substrn(a,1,2),4.0) as dzien format=4. label="to jest dzieñ",
11.         input(substrn(a,4,2),4.0) as miesiac,
12.         input(substrn(a,7,4),4.0) as rok,
13.         mdy(calculated dzien, calculated miesiac, calculated rok) as data format mmddyy10.
14.     from daty;
15.
16. *podzapytania nieskorelowane;
17. create table z1 as
18.     select idzamowienia, krajodbiorcy,
19.         fracht,datawysylki
20.     from n.zamowienia
21.     where fracht > (select avg(fracht) from n.zamowienia)
22.     and datawysylki is not null;
23.
24. *podzapytania skorelowane;
25. create table z2 as
26. select distinct a.idproduktu, a.cenajednostkowa as max
27.
28. from n.opisy_zamowien as a
29. where a.cenajednostkowa = (
30.     select max(cenajednostkowa)
31.     from n.opisy_zamowien as b
32.     where a.idproduktu = b.idproduktu
33.     )
34. order by a.idproduktu;
35.
36. *inner join;
37. create table z3 as
38. select distinct
39.     a.idproduktu
40.     ,a.cenajednostkowa as max
41. from n.opisy_zamowien as a
42. inner join (
43.     select idproduktu, max(cenajednostkowa) as max_cena
44.     from n.opisy_zamowien
45.     group by idproduktu
46. ) as b
47. on a.idproduktu = b.idproduktu and a.cenajednostkowa = b.max_cena
48. order by a.idproduktu;
49.
50. create table z4 as
51. select idklienta, nazwafirmy
52. from n.klienci
53. where idklienta in (
54.     select idklienta
55.     from n.zamowienia
56.     where datazamowienia > '01MAY1988'd
57. );
58.
59. create table z5 as
60. select
61.     y.idkategorii,
62.     y.nazwakategorii,
63.     round(x.actual, 2) as actual,
64.     round(y.planned, 2) as planned
65. from (
66.     select
67.         avg(a.cenajednostkowa) as actual,
68.         c.idkategorii
69.     from n.opisy_zamowien as a
70.     inner join n.produkty as b on b.idproduktu = a.idproduktu
71.     inner join n.kategorie as c on b.idkategorii = c.idkategorii
72.     group by c.idkategorii
73. ) as x
74. inner join (
75.     select
76.         a.idkategorii,
77.         b.nazwakategorii,
78.         avg(cenajednostkowa) as planned
79.     from
80.         n.produkty as a
81.         inner join n.kategorie as b on b.idkategorii = a.idkategorii
82.     group by a.idkategorii, b.nazwakategorii
83. )as y
84. on x.idkategorii = y.idkategorii;
85. quit;