【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语句的语法

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

 

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

 

三、Displaying All Columns

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

1 proc sql;
2      select *
3      from sasuser.staffchanges;

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

           当指定SELECT *语句时,PROC SQL中的FEEDBACK选项则会在日志中输出expand list of columns(每一列的详细名称)。

        例如:

1 proc sql feedback;
2     select *
3         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.

 

四、控制输出行的方法

  • 通过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=选项来控制。

 

        例子:

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

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

 

【SAS ADVANCE】Performing Advanced Queries Using PROC SQL,布布扣,bubuko.com

【SAS ADVANCE】Performing Advanced Queries Using PROC SQL

上一篇:Oracle管道函数(Pipelined Table Function)介绍


下一篇:修改数据库