SQL: Structured Query Language

一、Objectives in this chapter:

• invoke the SQL procedure
• select columns
• define new columns
• specify the table(s) to read
• specify subsetting criteria
• order rows by values of one or more columns
• group results by values of one or more columns
• end the SQL procedure
• summarize data 
• generate a report as the output of a query
• create a table of the output of a query

二、PROC SQL有何不同？

• 与其他PROC步骤不同的是，PROC SQL由很多个子句构成。最常见的情形是PROC SQL代码中包含两个语句：第一季是PROC SQL;第二季是SELECT语句，而SELECT语句中就包含多了子句，例如：SELECT ...,.. FROM ...,....WEHER和ORDER BY;
• PROC SQL过程不需要RUN语句，PROC SQL自动执行每一个查询；
• 建议在末尾加上QUIT语句来结束运行；

三、PROC SQL的基本格式

LIBNAME libref 'SAS-data-library'; 1 PROC SQL;                                             /*invoke the SQL procedure*/      2    CREATE TABLE table-name AS

3     SELECT column-1<,...,column-n>                   /*specifies the column(s) to be selectd*/4        from table-1|view-1<,...,table-n|view-n>      /*specifies the table(s) to be queried*/5        
     
                                  /*subsets the data based on a condition*/6        
      
       >             /*classifies the data into groups based  on the specifyed column*/7         
       
        >;           /*sorts the rows that the query returns by the values of the specified columns*/ 8  QUIT;
       
      
     

 

 【注意事项】：

    SELECT语句包含多个子句，以分号结束

四、PROC SQL输出

PROC SQL语句	TYPE Of Output
SELECT	report
CREATE TABLE and SELECT	table
CREATE VIEW AND SELECT	PROC SQL VIEW

 

 五、Order by语句中的编程注意事项

 

1) 常常放在select语句的最后；

2) 若用户需要逆序排序，则需将关键词DESC放在order by语句中需要逆序排列的变量后面，例如：

1 order by jobcode desc;

3) 在order by子句中，用户可通过指定select语句中变量的位置来代替其变量名，例如：

1 proc sql;2      select empid,jobcode,salary,salary*.06 as bonus3      from sasuser.payrollmaster4      where salary<320005      order by 2;

 

 

4）在order by语句中，通过逗号列出变量，进行多变量排序

1  proc sql;2       select empid,jobcode,salary,salary*.06 as bonus3       from sasuser.payrollmaster4       where salary<320005       order by jobcode,empid;/order by 2,empid;

 

 

六、join two or more tables 

• 若需要join两个或多个tables，list the columns that you want to select from both tables in the SELECT clause.
• Seperate all column names with commas.
• 若用户需要引用的变量名出现在多个表中，则需要在该变量前加上表明的前缀，来指定其表名；
• 若用户需要连接多个表，则需在from子句中加上多了表名，并用逗号隔开；
  1 proc sql;2     select salcomp.empid, lastname,3                newsals.salary,mewsalay4     from sasuser.salcomps, sasuser.newsals5     where salcomps.empid=newsals.empid6     order by lastname;

 

七、Summarizing Groups of Data

       If you specify a GROUP BY clause in a query that does not contain a summary function, your clause is changed to an ORDER BY clause, and a message to the effect is written to the SAS log.

1 proc sql;2     select membertype 3               sum(milestraveled) as TotalMiles4      from sasuser.frequentflyer5     group by membertype;6 /*输出每个membertype的milestraveled的和*/

 

 

八、HAVING语句：在含有GROUP BY子句的PROC SQL中筛选行

    A HAVING clause works with the GROUP BY clause to restrict the groups that are displayed in the output,based one or more specified conditions.

1 proc sql;2     select jobcode, avg(salary) as Avg3          from sasuser.payrollmaster4          group by jobcode5          having avy(salary)>400006          order by jobcode;