proc sql;
    describe table sashelp.cars;
    select *
    from sashelp.cars;
quit;

proc sql;
    select make, model, invoice
    from sashelp.cars(drop = MPG_: )
    /*where make = "Audi";*/
    /*where lowcase(make) eq "audi"*/
    /*where make = "Audi" or make = "BMW";*/
    /*where make in ("Audi", "BMW");*/
    where upcase(make) like "_A%";
    /*% - wiele znaków, _ - jeden znak*/
quit;

proc sql;
    create table z2 as
    select make, model, invoice
    from sashelp.cars(where=(make eq "BMW"))
    where horsepower >= 200;
quit;

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

*transformacje;

proc sql;
    create table z4 as
    select make length=200,
           model,
           type,
           0,2*invoice as rabat
            label = "Rabat w z1"
            format = commax10.2
    from sashelp.cars;
quit;

*case when;
proc sql;
    create table z5 as
    select make, model, invoice,
    case when invoice lt 40000 then "tanie"
         when invoice lt 60000 then "przecietne"
         else "drogie"
    end as kategoria
    from sashelp.cars;
quit;
*calculated;
proc sql;
    create table z6 as
    select make,
           model,
           4.5*invoice as invoice,
           case when calculated invoice lt 40000 then "tanie"
                when invoice lt 60000 then "przecietne"
                else "drogie"
           end as kategoria
    from sashelp.cars;
quit;

*top;
proc sql outobs=10;
    create table z7 as
    select make, model, max(invoice) as naj
    from sashelp.cars
    group by make
    having invoice = max(invoice)
    oreder by naj;
quit;

*monotonic;
proc sql;
    create table z8 as
    select make, model, invoice
    from sashelp.cars
    where monotonic() <= 10;
quit;