A*******s
发帖数: 3942 | 1 在data step里可以用_N_来获取row number的值，在proc sql里怎么做呢？
我知道可以用number option来显示row number，不过没法对这个值进行操作。 |
o****o
发帖数: 8077 | |
A*******s
发帖数: 3942 | 3 谢谢大牛
不过如果结合group by和summary function的话
好像结果很混乱，有解决方法么？
【在 o****o 的大作中提到】
: monotonic function
|
o****o
发帖数: 8077 | 4 what do you want to do?
【在 A*******s 的大作中提到】
: 谢谢大牛
: 不过如果结合group by和summary function的话
: 好像结果很混乱，有解决方法么？
|
A*******s
发帖数: 3942 | 5 use summary function + group by clause to generate some summary statistics with the order number. i can use the number option to show a column named "obs", but I want to change the name.
【在 o****o 的大作中提到】
: what do you want to do?
|
R******d
发帖数: 1436 | 6 举个例子看。我也想知道怎么用proc sql给出行号
with the order number. i can use the number option to show a column named "
obs", but I want to change the name.
【在 A*******s 的大作中提到】
: use summary function + group by clause to generate some summary statistics with the order number. i can use the number option to show a column named "obs", but I want to change the name.
|
l*********s
发帖数: 5409 | 7 can you add a column containing the row number first?
【在 A*******s 的大作中提到】
: 在data step里可以用_N_来获取row number的值，在proc sql里怎么做呢？
: 我知道可以用number option来显示row number，不过没法对这个值进行操作。
|
A*******s
发帖数: 3942 | 8 data test;
input X $ Y;
cards;
a 34
a 45
a 7
a 12
a 11
b 1
b 56
b 66
c 19
c 43
c 12
c 71
;
run;
可以用number option产生行号，但是不能改column name。用monotonic()的结果很混
乱，不大清楚这个函数的机制是什么。
proc sql number;
select monotonic() as Num, X, mean(Y) as mean
from test
group by X;
quit;
我也尝试了用subquery来搞，但是结果还是一样。
proc sql number;
select monotonic() as Num, * from
(select X, mean(Y) as mean
from test
group by X);
quit;
唯一行得通的方法是先create table as，再用monotonic()。那为啥subquery不行呢？
proc sql;
create
【在 R******d 的大作中提到】
: 举个例子看。我也想知道怎么用proc sql给出行号
:
: with the order number. i can use the number option to show a column named "
: obs", but I want to change the name.
|
A*******s
发帖数: 3942 | 9 不行啊，你加了summary function+group by就乱套了。
【在 l*********s 的大作中提到】
: can you add a column containing the row number first?
|
o****o
发帖数: 8077 | 10 group by _n_? what summarization do you want for individual record?
or do you want some odered cumulative statistics?
post some data step concepts first
with the order number. i can use the number option to show a column named "
obs", but I want to change the name.
【在 A*******s 的大作中提到】
: use summary function + group by clause to generate some summary statistics with the order number. i can use the number option to show a column named "obs", but I want to change the name.
|
|
|
y*m
发帖数: 102 | 11 this is the way how monotonic works,i guess.
if you add summary functions in the query, it's basically a re-merge, the id
num will be messed up.
seperate it into two steps instead.
【在 A*******s 的大作中提到】
: 不行啊，你加了summary function+group by就乱套了。
|
o****o
发帖数: 8077 | 12 is this what you want?
data test;
input X $ Y;
cards;
a 34
a 45
a 7
a 12
a 11
b 1
b 56
b 66
c 19
c 43
c 12
c 71
;
run;
proc sql ;
create table _number as
select monotonic() as Num, a.X, b. mean
from test as a
join (select X, mean(Y) as mean from test group by X) as b
on a.X=b.X
order by calculated Num
;
quit;
【在 A*******s 的大作中提到】
: 不行啊，你加了summary function+group by就乱套了。
|
A*******s
发帖数: 3942 | 13 不，我想要的结果是这样的
proc sql;
create table test1 as
select X, mean(Y) as mean
from test
group by X;
select monotonic() as Num, *
from test1;
quit;
但是我又不想产生一个间接的dataset。而且我不大明白为啥这样的结果和下面用
subquery的结果不一样。
proc sql number;
select monotonic() as Num, * from
(select X, mean(Y) as mean
from test
group by X);
quit;
【在 o****o 的大作中提到】
: is this what you want?
: data test;
: input X $ Y;
: cards;
: a 34
: a 45
: a 7
: a 12
: a 11
: b 1
|
o****o
发帖数: 8077 | 14 try this one. Counting on a newly generated variable so that the ordering
will be new
**********************;
proc sql;
create table new as
select monotonic(a.Y) as id, a.*
from (select X, mean(Y) as Y from test group by X) as a
order by calculated id
;
quit;
【在 A*******s 的大作中提到】
: 不，我想要的结果是这样的
: proc sql;
: create table test1 as
: select X, mean(Y) as mean
: from test
: group by X;
:
: select monotonic() as Num, *
: from test1;
: quit;
|
A*******s
发帖数: 3942 | 15 这话再肉麻我也要说：允许我膜拜一下全能的oloolo。就没有你搞不定的sas问题...
【在 o****o 的大作中提到】
: try this one. Counting on a newly generated variable so that the ordering
: will be new
: **********************;
: proc sql;
: create table new as
: select monotonic(a.Y) as id, a.*
: from (select X, mean(Y) as Y from test group by X) as a
: order by calculated id
: ;
: quit;
|
o****o
发帖数: 8077 | 16 那就给点伪币吧
【在 A*******s 的大作中提到】
: 这话再肉麻我也要说：允许我膜拜一下全能的oloolo。就没有你搞不定的sas问题...
|
A*******s
发帖数: 3942 | 17 已发:)
【在 o****o 的大作中提到】
: 那就给点伪币吧
|
