*4GI;
data z1;
set sashelp.cars;
run;

proc sql;
    create table zla as
    select *
    from sashelp.cars;

quit;

*typowe zapytanie;
proc sql;
create table z2 as
select*
from sashelp.cars
where make='Audi';
quit;

*drop jako opcja zbioru;
proc sql;
create table z2 as
select*
from sashelp.cars(drop=MPG_:);
quit;

*konkretne kolumny;
proc sql;
create table z2 as
select make, model, invoice
from sashelp.cars
where make='Audi';
quit;

*dla malych i duzych liter jednoczesnie;
proc sql;
create table z2 as
select make, model, invoice
from sashelp.cars
where lowcase(make) eq 'audi';
quit;

*operator OR;
proc sql;
create table z2 as
select *
from sashelp.cars
where make='BMW' or make='Audi';
quit;

*in;
proc sql;
create table z2 as
select*
from sashelp.cars
where make in ('BMW','Audi'); *maxymalnie do 1000 wzorcow;
quit;

*operator like ---samochody zaczynajace sie na a;
proc sql;
create table z2 as
select*
from sashelp.cars
where make like 'A%'; /* %zero lub więcej, _-jeden*/
quit;

*where kako opcja zbioru;
proc sql;
create table z2 as
select*
from sashelp.cars(where=(make eq 'BMW'))
where horsepower >= 200;
quit;

*length;
proc sql;
create table z2 as
select*
from sashelp.cars
where length(make)=4;
quit;

*distinct;
proc sql;
create table z2 as
select distinct type
from sashelp.cars;
quit;

proc sql;
create table z2 as
select distinct make, type
from sashelp.cars;
quit;

*przeksztalcenia;
proc sql;
create table z2 as
select make, invoice, 0.8*invoice as nowa_cena
from sashelp.cars;
quit;

*atrybuty kolumn;
proc sql;
create table z2 as
select make length=200,
    invoice,
    0.8*invoice as nowa_cena label='Cena w PLN'
    format commax20.2
from sashelp.cars;
quit;

*case when tzw else=if;
proc sql;
create table z3 as
select make, invoice,
    case
        when invoice lt 40000 then 'tanie'
        when invoice lt 60000 then 'przecietnie'
        else 'drogie'
    end as kategoria
from sashelp.cars;
quit;

*calculated;
proc sql;
create table z4a as
select make, 0.8*invoice as invoice,
    case
        when invoice lt 40000 then 'tanie'
        when invoice lt 60000 then 'przecietnie'
        else 'drogie'
    end as kategoria
from sashelp.cars;
quit;

proc sql;
create table z1a as
select make, invoice,
    case
        when calculated invoice lt 40000 then 'tanie'
        when calculated invoice lt 60000 then 'przecietnie'
        else 'drogie'
    end as kategoria
from sashelp.cars;
quit;

*liczba wierszy;
proc sql outobs=10;
create table z2 as
select*
from sashelp.cars;
quit;

proc sql;
create table z3 as
select*
from sashelp.cars
where monotonic()<=10;
quit;

*group by;
proc sql;
create table z2 as
select make, count(*) as liczba
from sashelp.cars
group by make;
quit;

proc sql;
create table z2a as
select make, type, count(*) as liczba
from sashelp.cars
group by make;
quit;

*kilka wyliczeń;
proc sql;
create table z3 as
select make, count(*) as liczba,
avg(invoice) as avg
from sashelp.cars
group by make;
quit;

*group by 1;
proc sql;
create table z3 as
select make, count(*) as liczba,
avg(invoice) as avg
from sashelp.cars
group by 1;
quit;

*where;
proc sql;
create table z3 as
select make, count(*) as liczba
from sashelp.cars
where type='Sedan'
group by make;
quit;

*having;
proc sql;
create table z3a as
select make, count(*) as liczba
from sashelp.cars
where type='Sedan'
group by make
having count(*)>10;
quit;

*max;
proc sql;
create table z4 as
select make, model, invoice
from sashelp.cars
group by make
having invoice=max(invoice)
order by invoice DESC;
quit;

*max;
proc sql;
create table z4a as
select make, model, invoice
from sashelp.cars
having invoice=max(invoice);
quit;

*podzapytanie;
proc sql;
create table z5 as
select origin, count(*) as liczba,
(count(*)/(select count(*) from sashelp.cars)) * 100 as udział
from sashelp.cars
group by origin;
quit;

*dodatkowe polecenia;
proc sql;
create table one(X num, A char(1));
insert into one
    Values(1,'a')
    Values(2, 'b');
quit;

proc sql;
drop table one;
quit;

proc sql;
create table one(X num, A char(1));
insert into one
Values(1, 'a')
Values(2, 'd')
Values(4, 'b');

create table two(X num, B char(1));
insert into two
Values(2, 'x')
Values(2, 'y')
Values(3, 'z');

create table three(X num, C char(1));
insert into three
Values(0, 'k')
Values(2, 'l')
Values(6, 'j');
quit;

*join;
proc sql;
create table l1 as
select one.x as one_x, one.A as one_a, two.*
from one, two;
quit;

*inner join;
proc sql;
create table l2 as
select one.x as one_x, one.A as one_a, two.*
from one, two
where one.x=two.x;
quit;

proc sql;
create table l2 as
select one.x as one_x, one.A as one_a, two.*
from one inner join two
on (one.x=two.x);
quit;

*aliasy;
proc sql;
create table l3 as
select a.x as one_x, a.A as one_a, b.*
from one as a inner join two as b
on (a.x=b.x);
quit;

*left join;
proc sql;
create table l4 as
select a.x as one_x, a.A as one_a, b.*
from one as a left join two as b
on (a.x=b.x);
quit;

*right join;
proc sql;
create table l5 as
select a.x as one_x, a.A as one_a, b.*
from one as a right join two as b
on (a.x=b.x);
quit;

*full join;
proc sql;
create table l5 as
select a.x as one_x, a.A as one_a, b.*
from one as a full join two as b
on (a.x=b.x);
quit;

*wiecej niz dwie tabele;
proc sql;
create table l6 as
select one.x as one_x, b, c, two.x as two_x, three.x as three_x
from one left join two on(one.x=two.x)
full join three on (one.x=three.x);
quit;