libname n 'C:\Users\student.WZIM.028\Desktop';

data daty;
a = "10.01.1988";
run;

proc sql;
    create table daty_1 as
    select *,

input(substrn(a,1,2),  4.0)
        as dzien format 4.0
        label = " to jest dzien",
        input(substrn(a, 4,2),4.0)
        as miesiac,

        input(substrn(a, 7,4),4.0) as rok,
        mdy(calculated dzien,
            calculated miesiac,
            calculated rok) as data2
            form mmddyy10.

            from daty;

*podzapytania nieskorelowane;

    create table z1 as
    select idzamowienia, krajodbiorcy,
    fracht, datawysylki
    from n.zamowienia
    where fracht > (select avg(fracht)
                    from n.zamowienia)
        and datawysylki is not null;

*podzapytania skorelowane;

        create table z2 as
        select distinct
                a.idproduktu,
                a.cenajednostkowa as max_cena

        from n.Opisy_zamowien as a
            where a.cenajednostkowa = (
                select max(cena jednostkowa)
                    from n.opisy_zamowien as b
                    where a.idproduktu = b.idproduktu
                                        )
        order by a.idproduktu;


        create table z3 as
        select a.idproduktu,a.cenajednostkowa
        from n.opisy_zamowien as a

        inner join
        (
            select id.produktu, max(cena jednostkowa)
            as max_cena
            from n.opisy_zamowien
            group by idproduktu
        ) as b

        on a.idproduktu = b.idproduktu and
         a.cenajednostkowa = b.max_cena

         order by a.idproduktu;

         *in;

         create table z4 as
         select idlienta, nazwafirmy
         from n.klienci
         where idklienta in
         (
            select idklienta from n.zamowienia
            where datazamowienia > '01MAY1988'd
        );



        proc sql;
        create table z5 as
        select y.idktegorii,
                y.nazwakategorii,
                x.acual,
                y.planned
        from
        (
            select avg(a.cenajednostkowa) as actual,
            c.idkategorii
            from n.opisy_zamowien as a

            inner join n.produkty as b ON
                b.idproduktu = a.idproduktu

            inner join n.kategorie as c ON
                b.idkategorii = c.kategorii

            group by c.idkategorii
        ) as x

        inner join

        (
            select a.idkategorii,
                    b.nazwakategrii,
                    avg(a.cenajednostkowa) as planned

            from n.produkty as a
            inner join n.kategorie as b ON
            b.idkategorii = a.idkategorii
            group by a.idkategorii, b.idkategorii
        )as y

        ON x.idkategorii = id.kategorii;
        quit;