【SAS ADVANCE】Performing Queries Using PROC SQL

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        <WHERE expression>                            /*subsets the data based on a condition*/
6        <GROUP BY column-1<,...column-n>>             /*classifies the data into groups based  on the specifyed column*/
7         <ORDER BY column-1<,...column-n>>;           /*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 bonus
3      from sasuser.payrollmaster
4      where salary<32000
5      order by 2;

 

 

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

1  proc sql;
2       select empid,jobcode,salary,salary*.06 as bonus
3       from sasuser.payrollmaster
4       where salary<32000
5       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,mewsalay
    4     from sasuser.salcomps, sasuser.newsals
    5     where salcomps.empid=newsals.empid
    6     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 TotalMiles
4      from sasuser.frequentflyer
5     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 Avg
3          from sasuser.payrollmaster
4          group by jobcode
5          having avy(salary)>40000
6          order by jobcode;

 

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

【SAS ADVANCE】Performing Queries Using PROC SQL

上一篇:数据库操作(SQLite)


下一篇:ORACLE常用命令