1.1:specifying Column Formats and Labels (SAS enhancements. )
procSQL Outobs= the; Title'Current Bonus Information'; Title2'Employees with salaries > $75,000'; /* Title can be placed before SQL or between SQL and select */ SelectEmpid Label='Employee ID',/*label= is placed after the variable */Jobcode label='Job Code', salary, ' salary is: ', Salary*.Ten asBonus/* inserts a fixed set of character constants into the list, or you can insert a numeric constant */format=Dollar12.2/*format= placed after the variable */ fromSasuser.payrollmasterwhereSalary>75000 Order bySalarydesc; Quit;
1.2:subsetting Data by Using subqueries (the table of the subquery can be different from the parent query)
A subquery selects one or more rows from a table, then returns single or multiple values (a subquery can return a value or values for an external query, if multiple values are returned, Then you need the corresponding conditional query symbol, such as in exists wait) to being used by the outer query
Two types of subqueries
Suboptimal: Unrelated sub-queries can be independent, efficiency than correlated sub-query efficiency is higher!
noncorrelated : A self-contained subquery that executes independently of the Outer query
proc SQL; Select Empid, LastName, FirstName, City, state from Sasuser.staffmaster where inch / * Unrelated, multiple return value instances * /(Select empid from where Month(dateofbirth)=2
correlated: A dependent subquery that requires one or more values to being passed to it by the outer query C5>before The subquery can return a value to the outer query,
Correlated subqueries is evaluated for each row of the outer query and, therefore, tend to require more processi ng time than noncorrelated subqueries. (The correlation subquery does not take place once, the external query will pass to the subquery once the data, therefore the efficiency is low)
Proc SQL;
Select LastName, FirstName
From Sasuser.staffmaster
where ' NA ' =
(Select Jobcategory
From Sasuser.supervisors
where staffmaster.empid = Supervisors.empid);
Quit
/* Program specific running steps in advance 91-100 Page view */
SQL (3) tag in SAS, formatted output, subquery, union query greater than two tables (n/a)