Sas sql-examples

proc sql;
create table ff as
select Branch_Cust_Nbr,
branch_nbr ,
mask_3 format=comma10.2
from events.Gidul_maskorot_10_1 a
where not exists
(select *
from events.Gidul_maskorot_9_1 b
where A.branch_cust_ip=b.branch_cust_ip);

quit;


proc sql;
create table jjj as
select Branch_Cust_Nbr,
branch_nbr ,mask_3
from events.Gidul_maskorot_10_1
where branch_cust_ip not in (select branch_cust_ip
from events.Gidul_maskorot_9_1);
quit;

proc sql;
create table jjj as
select Branch_Cust_Nbr,
branch_nbr ,mask_3
from events.Gidul_maskorot_10_1
where mask_3 > (select avg(mask_3)
from events.Gidul_maskorot_9_1);
quit;



proc sql;
create table jjj as
select Branch_Cust_Nbr,
branch_nbr ,mask_3/3 as avg_mask
from events.Gidul_maskorot_10_1
where branch_cust_ip not in (select branch_cust_ip
from events.Gidul_maskorot_9_1)
and
2*min_mask>calculated avg_mask;
quit;



proc sql;
create table res as
select coalesce (a.branch_cust_ip,b.branch_cust_ip) label='x'
from events.Gidul_maskorot_9_1 a
left join events.Gidul_maskorot_10_1 b
on a.branch_cust_ip=b.branch_cust_ip;
quit;


proc sql;
create table jjj as
select *
from events.Gidul_maskorot_10_1
except
select *
from events.Gidul_maskorot_9_1
;
quit;
/*delete double*/

proc sql;
create table jjj as
select *
from events.Gidul_maskorot_10_1
except all
select *
from events.Gidul_maskorot_9_1
;
quit;
/*not delete double*/


proc sql;
create table jjj as
select *
from events.Gidul_maskorot_10_1
except corr
select *
from events.Gidul_maskorot_9_1
;
quit;
/*relate only to variables this the same name */


proc sql;
create table jjj as
select *
from events.Gidul_maskorot_10_1
intersect
select *
from events.Gidul_maskorot_9_1
;
quit;

proc sql;
create table jjj as
select *
from events.Gidul_maskorot_10_1
union
select *
from events.Gidul_maskorot_9_1
;
quit;


proc sql;
create table jjj as
select *
from events.Gidul_maskorot_10_1
union
select *
from events.Gidul_maskorot_9_1
union
select *
from events.Gidul_maskorot_12_1
;
quit;

proc sql;
create table jjj as
select *
from events.Gidul_maskorot_10_1
outer union
select *
from events.Gidul_maskorot_9_1
outer union
select *
from events.Gidul_maskorot_12_1
;
quit;
SAS SQL