首页 > 代码库 > sas中的sql(3) 标签，格式化输出，子查询，大于两张表的联合查询（暂缺）
sas中的sql(3) 标签，格式化输出，子查询，大于两张表的联合查询（暂缺）
1.1：Specifying Column Formats and Labels （SAS enhancements. ）
proc sql outobs=15; title ‘Current Bonus Information‘; title2 ‘Employees with Salaries > $75,000‘; /*title可以放在sql之前或sql与select之间*/ select empid label=‘Employee ID‘, /*label=放在变量之后*/ jobcode label=‘Job Code‘, salary, ‘salary is:‘, salary * .10 as Bonus /*往列表中插入一列固定的字符常量，也可以插入数值常量*/ format=dollar12.2 /*format=放在变量之后*/ from sasuser.payrollmaster where salary>75000 order by salary desc; quit;
1.2：Subsetting Data by Using Subqueries（子查询的表可以和父查询不同）
A subquery selects one or more rows from a table, then returns single or multiple values（子查询可返回一个或多个值，用于外部查询，如果是返回多个值，那么需要对应的条件查询符号，比如in exists等待） to be used by the outer query
两种类型的子查询
优缺：不相关子查询可以独立进行，效率比相关子查询效率要高！
noncorrelated：a self-contained subquery that executes independently of the outer query
proc sql; select empid, lastname, firstname, city, state from sasuser.staffmaster where empid in /*不相关的，多个返回值实例*/ (select empid from sasuser.payrollmaster where month(dateofbirth)=2); quit;
correlated：a dependent subquery that requires one or more values to be passed to it by the outer query before the subquery can return a value to the outer query ，
Correlated subqueries are evaluated for each row in the outer query and, therefore, tend to require more processing time than noncorrelated subqueries.（相关性子查询没进行一次，外部查询就要往子查询中传入一次数据，所以效率偏低）
proc sql;
select lastname, firstname
from sasuser.staffmaster
where ‘NA‘=
(select jobcategory
from sasuser.supervisors
where staffmaster.empid = supervisors.empid);
quit;
/*程序具体运行步骤在advance中91-100页查看*/
sas中的sql(3) 标签，格式化输出，子查询，大于两张表的联合查询（暂缺）