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