SQL (2) row selection, restriction repetition, conditional operator, pre-run syntax check in SAS

1: Get the first few lines of data set observations

proc outobs=5 The *outobs option restricts the number of rows that are displayed and does not limit the number of rows that are read in. The inobs= option restricts    the number of rows that are read in; Select *          from sashelp.class;quit;data Res;     Set (obs=5); run;

2:eliminating Duplicate Rows from Output

Distinct:applies to all columns, and is those columns, that is listed in the SELECT clause.

proc SQL;     Select distinct Flightnumber, Destination    /*distinct can only follow the select * *   from sasuser.internationalflights; quit;

3: Conditional operator

To create a negative condition, can precede any of these conditional operators, except for any and all, with the not O Perator.

3.1:between value-1 and value-2 (between or equal to both ends of value are included)

To select rows based on a range of numeric or character values (value can make a number or a character ) when specifying the limits for T He range of values, it is not necessary to specify the smaller value first. (value-1/2 size is not required )

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 the columns of certain things are character types )

  proc sql Outobs=ten;          Select name                  from sasuser.frequentflyers            where contains ' ER ' ; quit;

3.3: InOperator 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. (the number of constants in parentheses is greater than or equal to 1)

3.4: Is MISSING or was NULL Operator to Select MISSING Values

To select rows this contain missing values, both character and numeric, use the was missing or is NULL operator. These operators is interchangeable.

(both character and numeric missing are checked , and the two symbols are equivalent)

where column = '; where column =.; Only the character and numeric deletions can be checked separately.

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  like ' % P%place '  * Spaces are also included in the string; 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-expressi ON) with the word or words (or other sql-expression) so you specify.

3.7:subsetting Rows by Using calculated Values (SAS-specific, not standard SQL)

procSQL Outobs=Ten; SelectFlightnumber, date, destination, boarded+Transferred+nonrevenue asTotal ,
　　　　　　 Calculated  TOTAL/2 as half fromsasuser.marchflightswhere  Calculated Total<  -; /*when you want to use the newly generated column, you need to add the calculated keyword, having to add the order by without adding*/

3.8:using the Any Operator

where dateOfBirth < any (subquery ...)

<any equal to Max () For example, the subquery returns 20, 30, 40, then the outer query selects all <40 records

>any equal to min () For example, the subquery returns 20, 30, 40, then the outer query selects all >20 records

=any equal to In

3.9:using the All Operator

All and any opposite

3.10:exsits, not exsits

For exsits, the output is true, and the false is not output.

For not exsits the opposite.

/ * Demand, choose the name of the person who is the employee and often flies alone * *
Procsql Title'Frequent Flyers who is Employees';
   SelectName
 fromSasuser.frequentflyers
    where exists 
(Select *   fromSasuser.staffmaster
 whereName=Trim (LastName)||', '||FirstName
Order  byName
Quit

4,NOEXEC, VALIDATE;

The same point : These two keywords have to make the program does not execute, only the effect of grammar check!

difference : Validate is valid only for the SELECT statement that follows, NOEXEC is valid for the SQL procedure

proc SQL noexec;        Select Empid, Jobcode, salary         from Sasuser.payrollmaster          where contains ' NA '           Order  by Salary;quit;

Proc SQL;
Validate
Select Empid, Jobcode, salary
From Sasuser.payrollmaster
Where Jobcode contains ' NA '
Order by salary;
Quit

SQL (2) row selection, restriction repetition, conditional operator, pre-run syntax check in SAS