*sql;

proc sql;

create table z1 as
select make, count(*) as liczba,
count(distinct type) as out type,
sum(invoice) as suma

from sashelp.cars
where origin  = "Europe"
group by make;
having liczba >= 3;

create table z2 as
select make, count(*) as cnt,
    sum(case when invoice >= 40 000 then 1 else 0 end) as col1,
    sum(case when invoice < 40 000 then 1 else 0 end) as col2,
    calulated cnt = calculated col1 + calculated col2 as col3,
sum(type= "Sedan") as sedan_cnt,
sum(type='SUV') as suv_cnt,
sum(type='Sports') as spr_cnt,


from sashelp.cars
grup by 1;


create table z3 as

select make, count(*) as cnt,
(count(*) / (select count(*) from sashelp.cars )) * 100 as col1
from sashelp.cars
group by make;


create table TWO( X num, A char(1));
 Insert into ONE values (1,'a')
                 values (4,'d')
                 values (2,'d');
drop table one;

create table  ONE( X num, B char(1));

Insert into ONE values(1,'a')
                values(1,'b')
                values(2,'c');

create table THREE( X num, C char(1));

Insert into three values(1,'x')
                    values(1,'y')
                    values(1,'z');
*produkt kartezjański;
create table z4 as
select t.*,b
from one as t, two;

*"inner join";
create table z4 as
select t.*,b
from one as t, two
where t.X = two.X;

create table z5 as

select t.*,b
from one as t inner join two on t.X = two.X;

*left join;
create table z6 as
select t.*,b
from one as t left join toe on t.X = two.X;


*full join;
create table z7 as
select
        coalesce( one.X, two.X) as X,
        one.X as one_X,
        a,
        two.X as two_X,
        b
from one 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;
    end;
    run;


*operatory;
proc sql;

create table z8 as
 select * from pierwsza
 union all /*suma nie usuwa duplikatów*/
 select* from druga;

 create table z9 as
 select * from pierwsza
 intersect /*część wspólna*/
 select * from druga;

create table z10 as
select * from pierwsza

except /*różnica*/
select * from druga;