标题: SQL语法请教 [打印本页] 作者: shiyiming 时间: 2004-10-8 14:53 标题: SQL语法请教 目的，将样本分为2个组（体重>=100排前面叫A组，体重<100排后面叫B组），AB组再按照年龄排序。
分后的数据应该：
[code:a532b]proc sql noprint;
create table WORK.CLASS1 as select * from SASHELP.CLASS where WEIGHT>=100;
create table WORK.CLASS2 as select * from SASHELP.CLASS where WEIGHT<100;
quit;
proc sort data=WORK.CLASS1; by DESCENDING AGE; run;
proc sort data=WORK.CLASS2; by DESCENDING AGE; run;
data WORK.CLASS; set WORK.CLASS1 WORK.CLASS2; run;
proc sql noprint;
drop table WORK.CLASS1;
drop table WORK.CLASS2;
quit;[/code:a532b]
而我觉得上面那段写的太烂了，想想有没有更高效的。
但是下面的程序始终有语法错误，我觉得逻辑对了。
[code:a532b]proc sql noprint;
create table WORK.CLASS like SASHELP.CLASS;
insert into WORK.CLASS select * from (select * from SASHELP.CLASS where WEIGHT>=100 order by DESCENDING AGE);
insert into WORK.CLASS select * from (select * from SASHELP.CLASS where WEIGHT<100 order by DESCENDING AGE);
quit;[/code:a532b]
请大家帮我看看。作者: shiyiming 时间: 2004-10-8 17:04 标题: ... [code:1e450]
proc sql ;
create table WORK.CLASS as
select * from SASHELP.CLASS where WEIGHT>=112
union
select * from SASHELP.CLASS where WEIGHT<112
order by age desc;
quit;[/code:1e450]作者: shiyiming 时间: 2004-10-8 18:38 标题: Re: ... willow大牛，谢谢！ <!-- s:lol: --><img src="{SMILIES_PATH}/icon_lol.gif" alt=":lol:" title="Laughing" /><!-- s:lol: -->
我脑子短路了，竟然没有想到union，惭愧啊，嘻嘻嘻。 <!-- s:oops: --><img src="{SMILIES_PATH}/icon_redface.gif" alt=":oops:" title="Embarassed" /><!-- s:oops: -->作者: shiyiming 时间: 2004-10-8 18:56 标题: ... 客气,客气作者: shiyiming 时间: 2004-10-10 16:03 标题: Re: ... [quote="willon":16a85][code:16a85]
proc sql ;
create table WORK.CLASS as
select * from SASHELP.CLASS where WEIGHT>=112
union
select * from SASHELP.CLASS where WEIGHT<112
order by age desc;
quit;[/code:16a85][/quote:16a85]
程序会不会只对后面一个SQL排序？即只对WEIGHT<112 排序．作者: shiyiming 时间: 2004-10-10 16:29 标题: to willon willon，的确有点问题：
Robert这个人被排在体重轻的那组了。
似乎结果集仅仅按照了年龄排序，没有分组的概念。作者: shiyiming 时间: 2004-10-12 00:33 标题: 。。。 如果我没有理解错的话，我打个比方：
create table tem as
select * from sashelp.class
order by age desc;
这段语句的意思是：
我要建一个表tem，内容是select * from sashelp.class，表tem以age字段倒序排序。
这里可能有个误会：是将select * from sashelp.class的内容放入tem中，然后表tem以age desc排序；而不是对select * from sashelp.class产生的内容进行age desc排序，然后将排序后的结果放入tem中。
order by是对create table 起作用的，order by的信息最终也依赖于保存在产生的新表中；select查询结果不能保存order by的信息，所以在用insert into的时候用order by会有语法错误。我上面程序中的order by也是对create table起作用的，不是对第二个查询起作用。
[code:1b48b]use tempdb;
if object_id('tem') is not null
drop table tem;
select top 0 * into tem from pubs..sales;
insert into tem select * from pubs..sales where qty>20 order by stor_id;
insert into tem select * from pubs..sales where qty<=20 order by stor_id desc;[/code:1b48b]作者: shiyiming 时间: 2004-10-13 10:54 标题: Re: SQL语法请教 第一次发贴先拿版主你开刀
data sashelp.class;
set sashelp.class ;
hehe=(weight>=100)*1000+age;
run;
proc sort;
by descending hehe;
run;
结果如下面
id age weight hehe
11 74 120.0 1074
12 74 103.0 1074
13 73 105.0 1073
14 73 102.0 1073
15 73 102.0 1073
16 72 110.0 1072
17 72 106.0 1072
18 72 102.0 1072
19 72 101.0 1072
20 71 110.5 1071
21 71 110.0 1071
22 51 108.0 1051
23 47 108.0 1047
24 40 105.5 1040
25 94 79.0 94
26 93 71.0 93
27 93 60.0 93
28 92 67.0 92
29 92 57.0 92
30 91 71.0 91
31 91 70.0 91
32 91 70.0 91
33 91 65.0 91
34 90 75.0 90
35 90 73.0 90
36 90 69.0 90
37 90 68.0 90
38 90 58.0 90作者: shiyiming 时间: 2004-11-3 08:03 标题: 改进一下 你既然加了一个hehe字段，何不简单明了一点：
data tmp;
set sashelp.class ;
if weight >=100 then flag=1;
else flag=2;
run;
proc sort data=tmp;
by flag descending age;
run;作者: shiyiming 时间: 2004-11-3 09:05 标题: 呵呵呵 :oops: 明了你到是算
简洁可能还是算我的吧
呵呵呵 <!-- s:oops: --><img src="{SMILIES_PATH}/icon_redface.gif" alt=":oops:" title="Embarassed" /><!-- s:oops: -->作者: shiyiming 时间: 2004-11-25 13:32 标题: 简单的方法 proc sql ;
create table WORK.CLASS as
select * from SASHELP.CLASS where WEIGHT>=112 order by age desc
union
select * from SASHELP.CLASS where WEIGHT<112 order by age desc;
quit;作者: shiyiming 时间: 2004-11-27 05:57 标题: a more simple way to do it proc sql noprint;
create table CLASS0 as
select * from SASHELP.CLASS
order by WEIGHT>=100 desc, age desc;
quit;作者: shiyiming 时间: 2004-12-1 14:40 标题: 加个组别识别的字段就可以了 proc sql ;
create table WORK.CLASS as
select *,1 as team from SASHELP.CLASS where WEIGHT>=112
union
select * ,2 as team from SASHELP.CLASS where WEIGHT<112
order by team,age desc;
quit;作者: shiyiming 时间: 2012-7-22 23:15 标题: Re: SQL语法请教 Mark