首页 > 代码库 > 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) 标签,格式化输出,子查询,大于两张表的联合查询(暂缺)