*SQL part 2;
proc sql;
    create table z1 as
    select make, count(*) as cnt,
        sum(invoice) as suma
    from sashelp.cars
    group by make;

proc sql;
    create table z2 as
    select make, count(*) as cnt,
        sum(invoice) as suma
    from sashelp.cars
    where type="sedan"
    group by make
    having count(*) > 10;

proc sql;
    create table z3 as
    select make, count(*) as cnt,
        count(distinct type) as cnt2,
        sum(case when invoice > 40000
            then 1 else 0 end) as col1,
        sum(case when invoice <= 40000
            then 1 else 0 end) as col2,
        calculated cnt = calculated col1 +
            calculated col2,
        sum(type="Sedan") as sedan_cnt,
        sum(type="SUV") as suv_cnt
    from sashelp.cars
    group by 1;

proc sql;
    create table One(x num, a char(1));
    insert into One
        values(1, 'a')
        values(4, 'b')
        values(5, 'c');

    drop table One;

    create table One(x num, a char(1));
    insert into One
        values(1, 'a')
        values(4, 'b')
        values(5, 'c');

    create table two(x num, a char(1));
    insert into two
        values(2, 'x')
        values(2, 'y')
        values(3, 'z');

    create table z4 as
    select t.*, b
    from one as t, two;

    create table z5 as
    select *
    from one as t, two
    where t.X = two.X;

    create table z6 as
    select t.*
    from one as t inner join two
    on t.x = two.x;

    create table z7 as
    select t.*
    from one as t left join two
    on t.x = two.x;

    create table z8 as
    select coalesce(t.x, two.x) as x
        ,t.x as x_one
        ,a
        ,two.x as x_two
        ,b
    from one as t full join two
    on t.x = two.x;
quit;;;;


data pierwsza;
do id=1 to 10 by 1;
kolumna_a = 'A';
kolumna_b = id*2;
output;
end;
run;

data druga;
do id=1 to 10 by 1;
kolumna_a='A';
kolumna_b=id*2;
if id=1 then kolumna_b=1;
if id=10 then kolumna_b=10;
output;
end;
run;

proc sql;
    create table z9 as
    select * from pierwsza
    union all
    select * from druga;

    create table z10 as
    select * from pierwsza
    union
    select * from druga;

    create table z11 as
    select * from pierwsza
    intersect
    select * from druga;

    create table z12 as
    select * from pierwsza
    except
    select * from druga;
quit;