【SAS ADVANCE】Performing Advanced Queries Using PROC SQL

一、Objectives

•  display all rows, eliminate duplicate rows, and limit the number of rows displayed
•  subset rows using other conditional operators and caculated values
•  enhance the formatting of query output
•  Using summary functions, such as COUNT, with and without grouping
•  subset groups of data by using the HAVING clause
•  subset data by using correlatedd and noncorrelated subqueries 
•  Validate query syntax.

二、SELECT语句的语法

SELECT column-1<,...,column-n>                       /*SELECT指定用户需要输出到output内的列*/
      FROM table-1|view-1<,...,table-n|view-n>       /*FROM指定去查询的table 或者view*/
      <WHERE expression>                             /*WHERE子句：用表达式来subset或者restrict数据集的条件*/
      <GROUP BY column-1<,...,column-n>>            /*GROUP BY按后面指定的列将数据集分成若干组*/
      <HAVING expression>                          /*HAVING子句：在group条件下，用表达式subset或者restrict分组后的数据*/
      <ORDER BY column-1<,...,column-n>>;         /*ORDER BY：根据其后的变量对查询结果进行排序*/

    【备注】：PROC SQL SELECT语句中的子句需要按照上述顺序排列。

三、Displaying All Columns

   1. 利用SELECT *：可将所有列呈现出来

proc sql;
     select *
     from sasuser.staffchanges;

   2. FEEDBACK选项（debugging tools：让用户可以清楚的看到what is being submitted to the SQL processor）

           当指定SELECT *语句时，PROC SQL中的FEEDBACK选项则会在日志中输出expand list of columns（每一列的详细名称）。例如：

proc sql feedback;
    select *
        from sasuser.staffchanges;

         则日志中会输出：

202 proc sql feedback;
203 select * 
204 from sasuser.staffchages;
NOTE: Statement tranforms to:
    
       select STAFFCHANGES.EmpID,
STAFFCHANGES.LastName, STAFFCHANGES.FirstName,
STAFFCHANGES.City, STAFFCHANGES.State,
STAFFCHANGES.PhoneNumber
       from SASUSER.STAFFCHANGES

       【备注】：日志不仅仅将星号（*）展开成详细列表，还会resolves macro variables and places parentheses around expressions to show their order of evaluation.

四、控制输出行的方法

        1.  通过OUTOBS=选项来限制 the Number of Rows Displayed

      General form:PROC SQL statment with OUTOBS= option:

      PROC SQL OUTOBS=n;  /*其中n指定了输出的行数，这里的OUTOBS=选项类似于DATA SET选项中的OBS=*/

         【备注】：这里的OUTOBS=选项仅仅是限制了display出来的行数，但是没有限制读入的行数。如果用户需要限制读入的行数，则可用INOBS=选项来控制。

          【例子】：

proc sql outobs=10;
     select flightnumber,date
         from sasuser.flightschedule; 

     日志提示：WARING: Statement terminated early due to OUTOBS=10 option.

 

         2.  通过SELECT语句中的DISTINCT关键词来剔除重复行

             【例子】：

/*代码1*/                                                /*代码2*/

proc sql outobs=12;                                     proc sql ;
     select flightnumber, destination                       select distinct  flightnumber, destination  
          from sasuser.internationalflights;                   from sasuser.internationalflights;
                                                                order by 1;/*按照SELECT语句中的第一个变量排序*/

            代码2输出的结果，相比代码1，将其重复的Flightnumber-Destination组合删掉了。

         3.  利用PROC SQL中的条件运算符subseting rows

           PROC SQL中的条件运算符包括三种：comparison（例如：where membertype='GOLD'）

                                                                      logical（例如：where visits<=3 or status='new'）

                                                                      concetenation（连接符，例如：where name=trim(last)||','||first）

                【例】：

proc sql;
     select ffid, name, state, pointsused
     from sasuser.frequentflyers
     where member='GOLD'  and pointsused>0
     order by pointsused;

 【其他的条件运算符】：

Conditional Operator	Tests for...	Example
BETWEEN-AND	valules that occur within an inclusive range	where salary between 70000 and 80000
CONTAINS或者?	values that是否含有某一指定字符串	where name contains ‘ER’ 等价于where name ? 'ER'
IN	values that match列表中的某一个值	where code in ('PT','NA','FA')
IS MISSING或IS NULL	缺失值	where dateofbirth is missing或where dataofbirth is null
LIKE(with %,_)	values that match a specified pattern	where address like '% P%PLACE'                                   （筛选出以字母P开头并以单词PLACE结尾的字符串）
=*	values that sounds like a specified value	where lastname =* 'Smith'
ANY	values that meet a specified condition with respect to any one of the values returned by a subquery	where dataofbirth <any(select dateofbirth from sasuser.parollmaster where jobcode='FA3')
ALL	values that meet a specified condition with respect to all the values returned by a subquery	where dataofbirth <all(select dateofbirth from sasuser.parollmaster where jobcode='FA3')
EXISTS	the existence of values returned by a subquery	where exists(select * from sasuser.flightschedule where fa.empid=flightschedule.empid)

           【备注】：

1. 上述条件运算符除了ANY, ALL和EXISTS外，其他都可以用于SAS的其他procedure中；
2. 若需要对条件进行否定，则只需在运算符前面加上NOT即可（对ANY和ALL不适用）；
3. 在CONTAIN运算符做匹配时，区分大小写；
4. 在IS MISSING或IS NULL语句中，也可以用where boarded=.（数值型）或where flight=' '（字符型）来判断；

   例子（供筛选的名字：Schmitt, Smith, Smithson, Smitt, Smythe）：
   
   where lastname =* 'Smith';
   
   上述供筛选的名字里面，只有Schmitt和Smithson没有被选中。

5. 在LIKE条件运算符中，_代表任意单个字符，而%代表任意sequence of zero or more characters.（注意，有时_和%也会被当做通配符）

   /*可供选择的几个名字列表：Diana, Diane, Dianna, Dianthus, Dyan*/
   /*LIKE Pattern*/                          /*Name(s) Selected*/
   LIKE 'D_an'                                      Dyan
   LIKE 'D_an_'                                     Diana, Diane
   LIKE 'D_an__'                                    Dianna
   LIKE 'D_an%'                                     上述所有名字

6.      =*（Sounds-like条件运算法）：souds-like利用SOUNDEX逻辑一次比较两个单词或表达式的每一列的值，筛选出任意contain a value that sounds like another value that you specify。

五、通过Calculated Values 来subsetting rows

       1.   PROC SQL运行Calculated Columns的原理

                 我们通过一个例子来说明PROC SQL运行calculated columns的原理，若在SAS中运行如下代码：

proc sql outobs=10;
     select flightnumber, date, destination, 
               boarded+transferred+nonrevenue as Total
            from sasuser.marchflights
            where total<100;

                在这段代码运行后，SAS会在日志中提示错误：ERROR: The following columns were not found in the contributing tables: total.

           【原因】：在SQL查询中，WHERE子句会比SELECT子句先执行。这样的话，SQL在表中查找WHERE子句中的变量名，而total此时还没有生成，因此会提示错误。

      2.  在WHERE子句中：利用关键词CALCULATED来引用SELECT子句中新定义的列

              用户可在WHERE子句中加入CALCULAED关键词，来引用新定义的列。例如：

proc sql outobs=10;
      select flightnumber, date, destination, 
                boarded+transferred+nonrevenue as Total
             from sasuser.marchflights
             where calculated total<100;

      3. 在SELECT子句中：利用关键词CALCULATED来引用SELECT子句中新定义的列

           若在SELECT子句中定义了一个类似与上面Total的新列，然后还需要在Total的基础上再定义第二个新列，则也需要引用CALCULATED关键词来实现，例如：

 proc sql outobs=10;
       select flightnumber, date, destination, 
                 boarded+transferred+nonrevenue as Total，
                 calculated total/2 as Half
              from sasuser.marchflights
              where calculated total<100;

六、改善Query输出

      1. 指定列的Format及Label

       在默认情况下，PROC SQL输出列的格式为查询的table中原有格式（若格式已保存在table中）或系统默认格式（若未保存在table中）。若用户需要控制输出的列的格式，可通过指定类似于LABEL=和FORMAT=等data set选项来实现。当用户在SELECT子句中定义一个新列示，除了分配一个新的名字外，也可以通过分配一个label来实现。

        【Data Set Option】: LABEL=    (select hiredate label='Date of Hire')/*若用户不希望在输出中出现label，则可以指定NOLABEL系统选项*/

                                            FORMAT=(select hiredate format=date9.)

         【例】

proc sql outobs=15;
    select empid labe='Employee ID',
              jobcode label='Job Code',
              salary,
              salary *.10 as Bonus format=dollar12.2
    from sasuser.payrollmaster
    where salary>75000
    order by salary desc;

    2. 指定Titles和Footnotes

        在PROC SQL查询中，若用户需要指定titles和footnotes，则须将TITLE和FOOTNOTE语句放在PROC SQL语句之前或者放在介于PROC SQL语句和SELECT语句之间。例如：

proc sql outobs=15;
title 'Current Bonus Information';
title2 ‘Employee with Salaries > $75,000’;
    select empid label='Employee ID',
              jobcode label='Job Code',
              salary,
              salary*.10 as Bonus format=dollar12.2
     from sasuser.payrollmaster
     where salary>75000
     order by salary desc;

   3. 在输出中增加Character Constant

          在PROC SQL中，用户可在SELECT子句中加入一个由引号括起来的字符串，这样就定义了一个类似于常值字符型的列。例如：

  proc sql outobs=15;
  title 'Current Bonus Information';
  title2 ‘Employee with Salaries > $75,000’;
      select empid label='Employee ID',
                jobcode label='Job Code',
                salary,
                ‘bonus is:’,
                salary*.10 format=dollar12.2
       from sasuser.payrollmaster
       where salary>75000
       order by salary desc;

        在运行上述代码后，output中就会在salary后输出两列：一列是常值字符串列（所有的value都为bonus is:），另一列是由salary*.10构成的格式为dollar12.2的列，此时就不需要再为其定义一个别名了。

七、 Summarizing和Grouping Data

        我们首先从一个例子出发，展示用summary函数来产生统计结果的方法：

proc sql;
    select membertype, avg(milestraveled) as AvgMilesTraveled
    from sasuser.frequentflyers
    group by membertype

    【SQL中影响summary函数的四个关键因素】（关键在于搞明白以下四种因素是怎么影响summary函数的作用的）

• whether the summary function specifies one or multiple columns as arguments
• whether the query contains a GROUP BY clause
• if the summary function is specified in a SELECT clause, whether there are additional columns listed that are outside of a summary function
• whether the WHERE clause, if there is one, contains only columns that are specified in the SELECT clause.

    1.  Summary函数及其参数个数

           summary函数的参数可以是一个，也可以是多个，并且放在圆括号内。

           有的函数，例如AVG、COUNT，是单参数函数。例如：proc sql; select avg(salary) as AvgSalary from sasuser.payrollmaster;

           而有一些函数，例如MEAN和N，适用于单个参数，也可适用于多个参数。例如：proc sql outobs=10; select sum(boarded, transferred, nonrevenue) as Total from sasuser.data;

    2.  作用在分组数据上的Summary函数

            若query子句中包含GROUP BY子句，则SELECT子句中所有没有被summary函数作用的列，都应该在GROUP BY子句中列出来，否则可能会出现意料之外的结果。

            【例】

proc sql outobs=10;
     select jobcode, avg(salary) as AvgSalary
     from sasuser.payrollmaster
     group by jobcode;

    3.  SELECT子句与Summary函数的作用方式

        在SELECT子句中，除了summary函数作用的列外，还可以列出其余没有被summary函数作用的列。SELECT子句中这些没有被summary函数使得PROC SQL展现出不同的output。下面举两个例子来说明这种不同：

 【例1】proc sql;    
          select avg(salary) as AvgSalary from sasuser.payrollmaster;

     例1的SELECT子句中，没有列是outside of summary functions的，因此在其输出结果中：

                           若没有指定了GROUP BY子句，则通过summary函数输出整张表的一个single value;

                           若指定了GROUP BY子句，则对每一个组输出summary函数作用的single value。

【例2】 proc sql;  
                 select jobcode, gender, avg(salary) as AvgSalary 
                  from sasuser.payrollmaster
                   group by jobcode,gender ;

                      若没有指定了GROUP BY子句，则通过summary函数输出整张表的一个single value;

                             若指定了GROUP BY子句，则对每一个组displays all the rows of output with the single or grouped values repeated。

   4. Argument（列）的个数对summary函数作用方式的影响

         我们通过下面两个列子来说明列的个数对summary函数作用方式的影响：若参数为1个，则对所有数据集作用

【例1】 proc sql;  
         select avg(salary) as AvgSalary from sasuser.payrollmaster; /*PROC SQL查询结果为所有员工的薪水的平均值*/

【例2】 proc sql outobs=10;
                 select sum(boarded, transferred, nonrevenue) as Total
                 from sasuser.marchflights;
/*由于summary function(sum)包含了多个参数，因此结果中每一行都计算了作用于多列参数的统计量。在这里，输出了一列10行的数据集，每一行是这三列的值的和*/

  5.  GROUP BY对summary函数作用方式的影响

          下面我们通过三种不同的情况下的summary函数的作用方式，来说明这三种情况的不同：

• without GROUP BY子句

  【例1】/*这里没有summary函数作的参数以外的列，因此仅输出一列：所有新水的平均值*/
   proc sql;  
           select avg(salary) as AvgSalary
            from sasuser.payrollmaster; 

• with Columns Outside of the Function

  【例2】/*这里的输出结果为20行的数据集，第一列jobcode（不是summary函数生成，所以不同）；第二列为所有的salary的和，为single value，每一行都相同*/
   proc sql outobs=20;
          select jobcode, avg(salary) as AvgSalary
            from sasuser.marchflights;

• with a GROUP BY子句

  【例3】/*这里的输出结果为对每一个jobcode分组的值，分别输出结果*/
   proc sql;
          select jobcode, avg(salary) as AvgSalary format=dollar11.2
            from sasuser.marchflights
            group by jobcode;