在SQL过程步中使用SAS数据步的选项
Using Data Set Options in PROC SQL
本文主要讲解了在SQL过程步中使用SAS数据步的选项，包括DROP、KEEP、LABEL、COMPRESS、SORTEDBY、WHERE和RENAME等，灵活地使用这些选项可以让我们的SQL过程步更有效、更简洁。附录创建了三个数据集，方便我们进行讲解。
1 DROP和KEEP
在使用用SQL过程步时，我们可以用select选择结果要保留的列，或者用*选择所有的列。但是如果我们要选择除某一两个列之外的所有的列，这时就要使用将其他的列全部都写一遍，是不是很麻烦。这时我们就可以用到DROP来删除这几个不需要的列。例如我们要保留除A1和A3列之外的SCORES数据集中的所有数据：
%let label=Scores Data Set without A1 and A3;
proc sql;
create table Scores1(label="&label") as
select *
from Scores(drop=A1 A3);
quit;
此外我们还可以直接在CREATE TABLE语句后面加上Drop或Keep选项来删除或保留指定的列：
proc sql;
create table Scores2(label="&label" drop=A1 A3) as
select *
from Scores;
quit;
下面一个例子是用多种方法来实现保留除B开头以外的所有列：
例1：这里KEEP选项里的A1-A10表示了变量范围，即：A1到A10共10个变量。注意这里的’ -’表示变量范围，如果在SQL过程步的select语句中，则表示用A1减去A10的值来创建一个新的变量。
proc sql;
create table only_As_1(label='Scores for A Parameters Only') as
select *
from Scores(keep=Subject_id Visit A1-A10);
quit;
例2： 这里用’--’表示名字范围，B1--B10表示从B1到B10共10个变量
proc sql;
create table only_As_2(label='Scores for A Parameters Only') as
select *
from Scores(drop=B1--B10);
quit;
例3：’B :’也是名字范围，指所有以B开头的变量
proc sql;
create table only_As_3(label='Scores for A Parameters Only') as
select *
from Scores(drop= B:);
quit;
例4：_numeric_指所有的数值型变量，与之相对应的是_character_，表示所有的字符型变量。
proc sql;
create table only_As_4(label='Scores for A Parameters Only') as
select *
from Scores(keep=Subject_ID _numeric_);
quit;
2 LABEL
这个选项在上面几个例子中已经讲解。
3 COMPRESS
COMPRESS主要是用来减少数据集的存储空间，在SQL过程步使用COMPRESS选项进行数据压缩时，我们需要在CREATE TABLE语句后要压缩的数据表后面加入该选项。
proc sql;
create table only_Bs(compress=yes) as
select *
from Scores(drop=A1-A10);
quit;
NOTE: 压缩的数据集 WORK.ONLY_BS 大小减少了百分之 54.85。
压缩为 484 页；不压缩将会要求 1072 页。
NOTE: 表 WORK.ONLY_BS 创建完成，有 60000 行，12 列。
4 RENAME
RENAME的作用是更改数据集的变量的名称，例如我们要将SCROES中的数组A的所有变量加上前缀’Base_’，你可以用select语句一个一个地更改名字，也可以用rename数据步选项。
例：用Select一个一个地进行更名
proc sql;
create table Baseline_A_1 as
select subject_id,
a1 as base_a1,
a10 as base_a10
from Scores
where visit=1;
quit;
例：用RENAME选项
proc sql;
create table baseline_A_2 as
select *
from Scores(rename=(a1-a10=base_a1-base_a10) drop=b:)
where visit=1;
quit;
注意，这里我们将数组A的名字改为以B开头的变量，而后面一句drop=b:是不是会把刚才更名完后的变量也删除掉。结果是不会的，因为Keep和Drop选项是在Rename选项之前就执行了。
下面来讲一个我们经常遇到的情况：两个表的内连接。例如我们要对表SCORES和SURGERY进行内连接，表SCORES的主键为subject_id，而表SURGERY的主键为sid，这时，我们一般的内连接语句为：
proc sql feedback _method;
create table Scores_Surg1 as
select *
from Scores T1, Surgery T2
where T1.subject_id=T2.sid and
T1.visit=T2.visit;
quit;
另一种方法就是先将SURGERY中的sid改名为subject_id，然后运用数据表的inner join来进行数据表的内连接：
proc sql feedback _method;
create table Scores_Surg2 as
select *
from Scores T1
natural inner join
Surgery(rename=(sid=subject_id)) T2;
quit;
另外，我们还可以遇到列名为SAS关键词时，会出现一些错误，例如：
proc sql _method;
create table caseLT4_1 as
select *
from Surgery
where case lt 4;
quit;
这里，where case这一句，SAS会认为这是一个条件操作的开始（case when…then… end），因此会出现错误。这时我们可以先将该列名更名后再进行where语句。例如：
proc sql _method;
create table caseLT4_2 as
select *
from Surgery(rename=(case=case_id))
where case_id lt 4;
quit;
这时case已改名为case_id
5 SORTEDBY
该选项主要指明数据集已用哪些变量进行排序，这会增加SQL过程步的效率。本人对该选项用得比较少，对效率优化也做得比较少，就不再介绍，请大家阅读相关文献。
6 WHERE
WHERE选项在SQL过程步中，主要用于取数据子集或指定连接条件。Surgery数据集中，visit小于16的客户在Scores数据中增加一个标识。一般的方法如下：
proc sql;
create table scores_surglt16 as
select T1.*,
case when ILV2.Visit gt 0 then 'Y' end as Surgery_Flag
from Scores as T1
left join
( select *
from Surgery
where Visit lt 16) as ILV2
on T1.visit=ILV2.visit and
T1.subject_id=ILV2.sid;
quit;
这里得到的结果是Surgery数据集中，visit小于16的10个客户，在Scores数据集中生成的新Surgery_Flag的值为Y。我们可以用where选项来简化上述代码：
proc sql;
create table scores_surglt16_2 as
select T1.*,
case when T2.Visit gt 0 then 'Y' end as Surgery_Flag
from Scores T1
natural left join
Surgery(where=(visit lt 16)
rename=(sid=subject_id)) T2;
quit;
where的另一个用法是与in选项结合。例如要实现模糊匹配，我们可以用like以及通配符%：
proc sql _method;
create table ex11a as
select *
from scores
where subject_id like '100-%' or
subject_id like '20%';
quit;
我们也可以用where和in选项来简化上述代码：
proc sql _method;
create table ex11b as
select *
from scores(where=(subject_id in: ('100-','20')));
quit;
7 附录
data Tx (label='Treatment Code Information' drop=a);
do a=1 to 3000;
Subject_No=a;
if a le 1500 then center =100; else center=200;
random_date=int(15000+20*ranuni(315975)); format random_date mmddyy10.;
if ranuni(97511) lt .5 then tx=1; else tx=2;
output;
end;
label tx='Treatment Code'
random_date='Randomization Date'
subject_no="Subject Number";
run;
data Scores(label='Recorded Scores at Visits' drop=j c);
length Subject_ID $8 Visit 8 A1-A10 8 B1-B10 $20;
array A[10]; array B[10];
do c=1 to 3000;
if c le 1500 then subject_id=compress('100-'||put(c,z4.));
else subject_id=compress('200-'||put(c, z4.));
do Visit=1 to 20;
do j=1 to 10;
a[j]=j*ranuni(j);
b[j]=left(put(.5+j**2*ranuni(j),7.3));
end;
output;
end;
end;
format a: 5.2;
run;
data Surgery;
length SID $8 Visit 8 case 8;
do k=5 to 3000 by 197;
if k lt 1500 then SID=compress('100-'||put(k,z4.));
else SID=compress('200-'||put(k, z4.));
Visit=10+int(10*ranuni(971156));
case+1;
drop k;
output;
end;
run;
0 comments:
Post a Comment