libname clinic 'E:\sas';
data admit;
    set sasuser.admit;
run;

proc sql;
    select name, sex , age, height 
    from sasuser.admit
    where (actlevel='LOW' and height>70) or age>35
    order by sex desc ,height asc;      /*排序*/
quit;

/*删除增加行*/

proc sql;
    delete from admit 
    where date=1;
quit;

proc sql;
    alter atble admit
    drop date;
quit;

proc sql;
    alter table admit
    add date num format=date9.,hh num format=comma10.2,xx char(3);
quit; 

proc sql;
    alter table admit
    modify xx char(20) label='xxxx';
quit; 



/*选择部分观测*/
proc sql;
    select  * from admit(firstobs=2 obs=10);
quit;

proc sql outobs=10;    /*inobs=10 对前10行进行操作*/
    select * from admit;
quit;

/********文本查询************/

proc sql number;
    select * from admit
    where name like 'P%';  /*name以P开头*/
quit;

proc sql;
    select * from admit
    where name like '%W';
quit;

proc sql;
    select * from admit
    where name not like '%ing%';
quit;

proc sql;
    select * from admit
    where name like '_ing, E';    /*引号里的内容必须完全===变量内容*/
quit;

proc sql;
    select * from admit
    where Name is missing; 
quit;    

proc sql;
    select * from sasuser.acities
    where name contains 'International'; *变量name中contains Internationl 子字符串的观察单位,查询;
run; 

proc sql;
    select * from admit
    where age between 30 and 50;
quit;

proc sql;
    select *from admit
    where name =*'smith';  /*模糊匹配，可以搜出smythe*/
quit;
data admitt;
    set sasuser.admit;
    if index(name,'ing')>0;
run;
proc print data=admitt;
run;

/*计算式条件查询（子查询）*/

proc sql;
    select actlevel, avg(height) as hh
    from admit
    group by actlevel
    having avg(height) > (select avg(height) from admit);  /***************/
quit;
/*any all  注意逻辑*/
proc sql;
    select name,height
    from admit
    where height = any(select height from sasuser.admitjune where sex="M");   /*admit中height大于任何一个admitJune女生身高*/
quit;

proc sql;
    select * 
    from admit
    where exists (select * from sasuser.added where i=6);   /*只要brackets里面执行结果不为null，则外面的sql执行*/
quit;
 /*except去重*/
proc sql;
    select name,maxhr
    from sasuser.stress98  /*98中去掉98a中出现的名字*/
    except               
    select name
    from sasuser.stress98a;
quit;

proc sql;
    select name, maxhr        /*重复的名字不去掉*/
    from sasuser.stress98
    except ALL
    select name
    from sasuser.stress98a;
quit;
/*保留所有重复的列和行*/
proc sql;
    select *
    from sasuser.stress98
    except corr
    select *
    from sasuser.stress98a;
quit;
/*intersect查重*/
proc sql;
    select name
    from sasuser.stress98  
    intersect                     /*选出98中98a出现的名字*/
    select name
    from sasuser.stress98a;
quit;
proc sql;
    select *
    from sasuser.stress98
    except    corr
    select *
    from sasuser.stress98a;
quit;

/*union /  outer union*/

/*创建空表*/
proc Sql;  /*自定义的*/
    create table datt
    (name char(30), sex char(2), age num ,begin num format date9. label ='begin timing');
quit;

proc sql; /*用某个表的列名称和格式*/
    create table stresss
    like sasuser.stress98;
quit;


proc sql outobs=0;
    create table sass as 
    select *
    from sasuser.stress98;
quit;
/*逻辑核查（建库）*/
proc Sql;  
    create table datt
    (name char(30), sex char(2), age num ,begin num format date9. label ='begin timing',
    height num, 
    constraint height  check (height le 3));  /*height值不能大于3*/
quit;
proc sql;
    insert into datt
    set height=3.01;                           /*会报错*/
quit;

 /*插入一行观测*/
proc sql;
    insert into admit (name,age) values ( 'yangbo',25); 

quit;

proc sql ;
    insert into admit values ('1234' ,'wt','F',26,1,155,45,'HH',22.2);
quit;

proc sql;
    insert into admit
    set 
        name='xx',
        age=8;
quit;


/*更新data*/

proc sql;
    update admit set height=1.55,sex='n'
    where name='wt';
quit;

/*验证语法正确*/

proc sql;
    validate
    select name, BMI
    from admit;
quit;

proc sql noexec;
    select name, BMI
    from admit;
quit;

/*计算已有变量创建新变量*/
proc sql;
    select name , height*height/weight as BMI
    from admit
    where calculated BMI > 30;        /*note clause calculated*/
quit;


/*查询列*/

proc sql feedback noprint;  /*check log*/
    select * from admit;
quit;

/*label format*/

proc sql;
    select name lable="xxx" , fee format=dollar12.2
    from admit;
quit;

/*常用统计描述*/
/*一列求和，纵向*/
proc sql; 
 select sum(height),name  /*第一列均为height之和,且无变量名*/
 from admit;
quit;
/*多列求和，横向*/
proc sql;
    select name , sum(fee,height) as aa
    from admit;
quit;
/*按组*/
proc sql; 
 select sum(height),name  /*第一列均为height之和,且无变量名*/
 from admit
group by sex;
quit;
/*计数*/
proc sql;
    select distinct (actlevel) as level ,count(*) as count
    from admit
group by actlevel;
quit;
/*distinct查看取唯一值*/
proc sql;
    select distinct (actlevel) as level
    from admit
quit;

proc sql;
    select actlevel  ,avg(height) as H 
    from sasuser.admit
    group by actlevel;
quit;


/**************拼表**************/
/*内连接*/
data sasuser.stress98a;
    set sasuser.stress98;
    where maxhr >170 and maxhr < 190;
    keep name year hr;     /*单独修改了一个name，Disco, 6*/
    hr+maxhr;            /*添加一列*/
run;

proc sql;
    create table nnnerj as    /*创建表和直接显示结果不一样，创建表中没有重复显示变量*/
    select *
    from sasuser.stress98 join sasuser.stress98a
    on stress98.name=stress98a.name;  /*相当于98a中有部分98中的人，且a有这部分人其他变量，最后拼出来：共有的人、所有变量*/
quit;
/*左外拼*/
proc sql;
    create table leftout as      /*左表不动，添加右表中能在左表用得到的信息*/
    select *
    from sasuser.stress98 left join sasuser.stress98a
    on stress98.name=stress98a.name;
quit;
/*右外拼*/
proc sql;
    create table rightout as   /******有一个问题******/  /*在创建表时，省去了重复变量，但是没右表独有的值没显示（重复变量中）*/
    select *
    from sasuser.stress98 right join sasuser.stress98a
    on stress98.name=stress98a.name;
quit;
/*全连接*/
proc sql;
    select *
    from sasuser.stress98 full join sasuser.stress98a
    on stress98.name=stress98a.name;
quit;
/*笛卡尔积*/
proc sql;
    select *
    from sasuser.stress98 cross join sasuser.stress98a;
quit;
/*创建视图*/
proc sql;
    create view vadmit as
    select * 
    from admit;
quit;

/*条件赋值*/
proc sql;
    select *,
    (case when sex="M" then 1 else 2 end )as group 
    from admit;
quit;