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.
注意这里一个细节，distinct的变量会默认排序
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中的）
sas编译时，先执行where,如果不用calculated那么就会报错说没有total这个变量，加上后会在新生成的变量中查找。
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 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;
proc sql;
validate
select empid, jobcode, salary
from sasuser.payrollmaster
where jobcode contains 'NA'
order by salary;
quit;
4.1 feedback,在列比较多，我用来查看列名。。。。然后复制粘贴我想要的名字。。。
*options fullstimer=on;*run; proc sql feedback; select * from sashelp.class; NOTE: 语句变换为:select CLASS.Name, CLASS.Sex, CLASS.Age, CLASS.Height, CLASS.Weightfrom SASHELP.CLASS;quit;
4.2Count
只计算非缺失值