PROC SQL过程语句
proc sql;
create table 创建一个数据表
drop table 删除数据表、视图或索引
select 查询数据表中的数据 1.可包括一个或多个SELECT语句。2.SELECT语句必须包括SELECT子句和FROM子句。3.SELECT语句中的变量顺序非常重要
insert 对数据表插入数据
update 对已存在的数据集的列的值进行修改
delete 删除数据表中的行
alter 增加、删除或修改数据表的列
quit;
/*查询*/
/*proc sql;*/
/*create table as*/
/*select 列1,列2 from 表1*/
/*where 筛选条件 */
/*group by 分组变量=class,by?/*?具体碰到的时候看:GROUP BY需要配合汇总函数使用,如果没有汇总函数,那么与ORDER BY 相同;常用汇总函数:avg,mean,count,freq,n,cv,max,min,miss,range,std,sum等等*/*/
/*having 筛选条件*/
/*order by 排序变量=proc sort by 对by后变量进行排序;
/*quit;*/
proc sql;
create table lily as
select sex from sashelp.class
where sex="M"/*WHERE语句在SELECT语句之前执行*/
order by age, name;
quit;
/*创建空表:用其他表结构*//*(create table B like A)*/
proc sql;
create table lily like
sashelp.class;/*quit前一步勿忘加;*/
quit;
/*创建表:创建一个数据和结构都相同的表*//*(create table B as select* from A)*/
proc sql;
create table lily as
select * from sashelp.class;/*select 某个变量呢?*/
quit;
/*插入数据:从其他数据集copy*//*1.插入数据前要有文件表B*//*2.(insert into B select * from A)*/
/*插入全部用* */
data lily1;
set lily;
run;
proc sql;
insert into lily1
select * from sashelp.class;
quit;
/*插入部分变量va1,va2 ,注意格式类型匹配问题???*/
data lily1;
set lily;
run;/*插入数据前要有文件表B*/
proc sql;
insert into lily1
select name,age from sashelp.class;
/*1只select一部分变量呢?不是全部* */
/*2insert select列表一样 多,类型一致error???SELECT语句中的变量顺序非常重要*/
quit;
/*生成新变量Ratio*//*(select var1,va2/var3 as newvar FORMAT=5.2)*/
proc sql;
create table lily as
select *,weight/height as Ratio FORMAT=5.2
from sashelp.class;
quit;
/*使用函数*//*(select min(var原) as var现 )按不同性别去筛选最小、最大和平均年龄。*/
proc sql;
create table class1 as
select sex, min(age) as youngest,
max(age) as oldest,
mean(age) as average
from class group by sex;
quit;
/*Distinct类似与proc sort nodupkey*//*(select distinct x,y from ) 获得x和y且不重复的数据*/
proc sql;
create table class1 as
select distinct sex, age
from class;
quit;
/*宏变量*//*(select into: from)*/
/*将class数据集的人数放入number宏变量 跑出来结果?有点不懂拿来干啥*/
proc sql;
select count(name) into:number
from sashelp.class;
quit;
/*合并数据集*/
/*纵向合并=set*/
/*(select * from A */
/*outer union corr */
/*select * from B)*/
proc sql;
create table lilllly as
select * from sashelp.aacomp
outer union corr /*纵向合并不会删除重复行,会合并ID列?*/
select * from sashelp.aarfm;
quit;
/*横向合并=merge:left jion-左表所有+右表中共同的 right jion; full jion; inner jion */
/*(select a.var1,a.var2,b.var4*/
/*from work1 as a left join work2 as b*/
/*on a.ppl=b.ppl)*/
proc sql;
create table lil as
select a.*,b.uppermean,b.lower/*共同变量只能选一次*/
from sashelp.class as a left join sashelp.classfit as b
on a.name=b.name; /*ppl为共同变量*/
quit;