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

Source: Internet
Author: User
Tags repetition

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.