m****d
发帖数: 331 | 1 希望从原来的表格中，按照客户ID， 得到两种状态转化所用的时间，例如，ID1从状态
4 到 3 用了8天。 请教怎么得到所希望的表格，谢谢！给出正确答案的前三名者，每
个人两个包子 （伪币20）！！ |
p***7
发帖数: 535 | 2 I don't have much time to give the details. I think using 'if and do loop.'
will be fine.
Thanks |
d********h
发帖数: 2048 | 3 都不用sql，直接用lag function，然后求差，transpose，就是你要的。
【在 m****d 的大作中提到】
: 希望从原来的表格中，按照客户ID， 得到两种状态转化所用的时间，例如，ID1从状态
: 4 到 3 用了8天。 请教怎么得到所希望的表格，谢谢！给出正确答案的前三名者，每
: 个人两个包子 （伪币20）！！
|
l****u
发帖数: 529 | 4 if you have to use SQL , case expression can help you deal with that kind of
situation.
select *, max( case when status=4 then date else . end ) as date4,
max( case when status=3 then date else . end) as date3,
.......etc.,
calculated date4-calculated date3 as form43,
.......etc.
from yourdata
group by id |
H******9
发帖数: 8087 | 5 厉害
【在 d********h 的大作中提到】
: 都不用sql，直接用lag function，然后求差，transpose，就是你要的。
|
k*******a
发帖数: 772 | 6 A stupid way:
data test;
input Ids $ Status Date mmddyy10.;
datalines;
ID1 4 05/02/2013
ID1 3 05/10/2013
ID1 2 05/16/2013
ID1 1 05/20/2013
ID2 3 05/08/2013
ID2 2 05/10/2013
ID2 1 05/19/2013
;
run;
proc sql;
select distinct Ids,
(select date from test where Ids = a.Ids and status=3) - (
select date from test where Ids = a.Ids and status=4) as chg43 label = "From
4-3 (Days)",
(select date from test where Ids = a.Ids and status=2) - (
select date from test where Ids = a.Ids and status=3) as chg32 label = "From
3-2 (Days)",
(select date from test where Ids = a.Ids and status=1) - (
select date from test where Ids = a.Ids and status=2) as chg21 label = "From
2-1 (Days)"
from test a;
quit; |
A*******s
发帖数: 3942 | 7 如果非要用sql，搞明白什么是self join就行了。
【在 m****d 的大作中提到】
: 希望从原来的表格中，按照客户ID， 得到两种状态转化所用的时间，例如，ID1从状态
: 4 到 3 用了8天。 请教怎么得到所希望的表格，谢谢！给出正确答案的前三名者，每
: 个人两个包子 （伪币20）！！
|
s********a
发帖数: 154 | 8 select isnull(isnull(A.Name, B.name), O.Name) as Name, (A.date - B.date) as
AB, (B.date - O.date) as BO from
((select * from tb where state = 'st1') as A
full join (select * from tb where state = 'st2') as B on A.Name = B.Name)
full join (select * from tb where state = 'st3') as O
on B.Name = O.Name;
attributes 和 table 名字改成你相应表格的试试看 |
