sas中的sql(2) 行选择 、限制重复、条件运算符、运行前语法检查

1：获取数据集前几行观测

proc sql outobs=5; *outobs选项只限制显示的行数，并不限制读入的行数. inobs=选项可以限制读入的行数;
    select * 
        from sashelp.class;
quit;

data res;
    set sashelp.class (obs=5); 
run;

2：Eliminating Duplicate Rows from Output  

DISTINCT ： applies to all columns, and only those columns, that are listed in the SELECT clause.

proc sql;    
select distinct flightnumber, destination     /*distinct只能跟在select后*/
from sasuser.internationalflights; 
quit;

3：条件运算符

 To create a negative condition, you can precede any of these conditional operators, except for ANY and ALL, with the NOT operator. 

3.1：BETWEEN value-1 AND value-2  （ between or equal to 两端的value是被包括进去的）

To select rows based on a range of numeric or character values（value可以使数字也可以是字符），When specifying the limits for the range of values, it is not necessary to specify the smaller value first. （value-1/2的大小无要求）

3.2:Using the CONTAINS or Question Mark (?) Operator to Select a String  

sql-expression CONTAINS/? sql-expression 

where sql-expression is a character column, string (character constant), or expression(contain某些东西的列是字符型)

  proc sql outobs=10;   
      select name        
        from sasuser.frequentflyers       
    where name contains ‘ER‘; 
quit;

3.3:IN Operator to Select Values from a List  

column IN (constant-1 <, . . . constant-n>) 

constant-1 and constant-n represent a list that contains one or more specific values.(括号中的常量个数大于等于1)

3.4： IS MISSING or IS NULL Operator to Select Missing Values  

To select rows that contain missing values, both character and numeric, use the IS MISSING or IS NULL operator. These operators are interchangeable.  

（字符型和数值型缺失都可检验，这两个符号是等价的）

where column = ‘ ‘; where column = .;分别只能检验字符型和数值型缺失。

3.5： LIKE Operator to Select a Pattern 

column LIKE ‘pattern‘  

underscore ( _ )  any single character 

percent sign (%)  any sequence of zero or more characters

proc sql;    
     select ffid, name, address     
       from sasuser.frequentflyers        
 where address like ‘% P%PLACE‘; *空格也包含在字符串中;
quit;

3.6：Using the Sounds-Like (=*) Operator to Select a Spelling Variation 

The sounds-like (=*) operator uses the SOUNDEX algorithm to compare each value of a column (or other sql-expression) with the word or words (or other sql-expression) that you specify.

3.7：Subsetting Rows by Using Calculated Values （sas特有的，不是标准sql中的）

proc sql outobs=10;    
     select flightnumber, date, destination,      
          boarded + transferred + nonrevenue as Total,
　　　　　　calculated Total/2 as half                
         from sasuser.marchflights         
   where calculated total < 100;  /*想要使用新生成的列的时候，需要加上calculated关键字,having要加 order by 不用加*/

3.8：Using the ANY Operator 

where dateofbirth < any   (subquery...)              

<any equal to max()    比如，子查询返回20 、30、 40，那么，外查询选择所有<40的记录

>any equal to min()　  比如，子查询返回20 、30、 40，那么，外查询选择所有>20的记录

=any equal to in

3.9：Using the ALL Operator 

all和any相反

3.10：exsits 、not exsits

对于exsits，为真的话就输出，假的就不输出。

对于not exsits相反。

/*需求，选择是员工又是经常单独飞行的人姓名*/
proc sql; title ‘Frequent Flyers Who Are Employees‘;  
   select name 
   from sasuser.frequentflyers     
    where exists 
(select *  from sasuser.staffmaster 
 where name=trim(lastname)||‘, ‘||firstname) 
 order by name;
 quit; 

4、NOEXEC 、VALIDATE;

相同点：这两个关键字都有使程序不执行，只进行语法检查的效果！

不同点：validate只对紧跟其后的select语句有效，noexec对真个sql过程有效

proc sql noexec;       
select empid, jobcode, salary   
    from sasuser.payrollmaster     
    where jobcode contains ‘NA‘       
    order by salary;
quit;