SAS SQL语句

[谢远涛]

/*1 SQL查询*/
/*1.1 简介*/
PROC SQL;
CREATE TABLE CLASS AS
SELECT height,weight,name  FROM XIE.CLASS
WHERE SEX="男";
QUIT;

/*1.2 单表查询*/
PROC SQL;
CREATE TABLE A1 AS
SELECT height,weight,name  
FROM XIE.CLASS
WHERE age>12
GROUP BY sex
ORDER BY height;

SELECT name, height 
FROM XIE.CLASS 
WHERE name='托马斯';

/*(一)选择列表SELECT*/
/*1、选择所有列 */
SELECT * 
FROM xie.class;

/*2、选择部分列并指定它们的显示次序 */
SELECT name,height 
FROM xie.class; 

/*3、更改列标题 */
SELECT name as 姓名, height as 身高
FROM xie.class;

/*4、删除重复行 */
SELECT ALL SEX,AGE
FROM xie.class;  
SELECT DISTINCT SEX,AGE
FROM xie.class; 

/*5、选择不同的行 */
SELECT DISTINCT age 
FROM xie.class;

/*
data xie.class_score ;
merge xie.class xie.student;
if name="" then delete;
drop sex age height weight;
run;
*/

proc sql noprint;
select age, sex
into :age, :sex
from xie.class
where name="简";

%put &age &sex;

/*(二)FROM子句*/
PROC SQL;
SELECT class_score.name,sex 
FROM xie.class,xie.class_score 
WHERE class.name=class_score.name; 


SELECT a.name,sex 
FROM xie.class AS a,xie.class_score AS b
WHERE a.name=b.name; 

SELECT a.name,sex 
FROM xie.class  a,xie.class_score b
WHERE a.name=b.name; 

SELECT a.name,b.math,total.sum 
FROM xie.class  a,xie.class_score b,
(SELECT name,sum(math,phys,chem,literat,history,english) AS sum
FROM xie.class_score ) AS total 
WHERE a.name=b.name=total.name AND total.sum>360;

/*(三)使用WHERE子句设置查询条件*/
SELECT * 
FROM xie.class 
WHERE age>12; 

SELECT * 
FROM xie.class 
WHERE age between 10 and 30; 

SELECT * 
FROM xie.class 
WHERE age in(12,13,14); 

SELECT * 
FROM xie.class 
WHERE name like "%%斯"; 


SELECT * 
FROM xie.class 
WHERE age not is null; 

/*(四)查询结果分组GROUP BY、排序ORDER BY*/
SELECT * 
FROM xie.class 
ORDER BY age desc,name ASC ;


SELECT * 
FROM xie.class 
GROUP BY sex ;

/*(五)函数*/

/*(六)其他语句*/
/*1.CREATE(创建表, 索引, 视图, 同义词, 过程, 函数, 数据库链接)*/
CREATE TABLE a1 AS
SELECT *
FROM xie.class;

/*2.ALTER(改变表, 索引, 视图等)*/
/*在表的后面增加一个字段*/
ALTER TABLE a1 ADD average num;
/*删除一个字段*/
ALTER TABLE a1 drop average;


DROP TABLE a1;

CREATE TABLE a1 AS
SELECT *
FROM xie.class;
DELETE FROM a1;

INSERT INTO A1 VALUES ( 'ZHANG','男',12,66,120);

UPDATE A1 
SET SEX='女'
WHERE NAME='ZHANG';

/*1.3 跨表查询*/
CREATE TABLE a2 AS
SELECT a.name,a.sex,b.sum
FROM xie.class a,
(SELECT name,sum(math,phys,chem,literat,history,english) AS sum
FROM xie.class_score ) AS b
WHERE a.name=b.name ;

/*1.4 嵌套查询*/

/*1.5 案例*/
proc sql;
/*创建表*/
create table student
(Sno char(4)NOT NULL UNIQUE,/*列级完整性约束条件：取值唯一，不许缺失*/
Sname CHAR(20)  UNIQUE,/*列级完整性约束条件：取值唯一*/
Ssex char(1),
Sdept char(3),
Salary num,
Birth num informat=date7.
format=date7.,
Hired num informat=date7.
format=date7.);
/*写入信息*/
   insert into student
 values('1639', '李四', 'F','TA1',42260,'26JUN70'd,'28JAN91'd)
 values('1065', '张立', 'M','ME3',38090,'26JAN54'd,'07JAN92'd)
 values('1400', '李勇', 'M','ME1',29769.'05NOV67'd,'16OCT90'd)
 values('1561' ,'刘晨', 'M',null,36514,'30NOV63'd,'07OCT87'd)
 values('1221', '王敏', 'F','FA3',.,'22SEP63'd,'04OCT94'd ) ;
/*修改*/
alter table student
add  age num;
/*创建查询*/
title 'PROCLIB.PAYLIST Table';
select *
from student;
/*删除表*/
drop table student;
quit;