sas sql中有类似mysql的格式_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 asBonus /*往列表中插入一列固定的字符常量，也可以插入数值常量*/

format=dollar12.2 /*format=放在变量之后*/

fromsasuser.payrollmasterwhere 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 thatexecutes independently of the outer query

PROC SQL always evaluates a noncorrelated subquery before the outer query哪个subaquery的层次最深就先执行哪个

procsql;selectempid, lastname, firstname, city, statefromsasuser.staffmasterwhere 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页查看*/