proc sql;

[sss]

HI All,
I want to find TBF(Time Between Failure) with respect to id. Sampe data goes here...................

data new;
input id$ date:date9.;
FORMAT DATE DATE9.;
datalines;
a 15aug2007
a 21apr2009
b 05may2009
c 15may2009
d 01feb2010
d 01mar2010
;
run;

output
====================
ID date TBF
a 15aug2007 .
a 21apr2009 615
b 05may2009 .
c 15may2009 .
d 01feb2010 .
d 01mar2010 28

I tried with oracle code it is working fine .code goes here

select *, TRUNC(TO_CHAR(date- lag(date,1) OVER (PARTITION BY assetnum ORDER BY faildate)),2) AS TBF_Days from table1
by id;

but when i am tring to execute same code in proc sql then i m getting error msg like
PARTITION BY not define lag cant be used in proc sql . I think there are restriction are there .


I want to get the put using proc sql.

I tried with Lag and dif function in Data Step but not getting the output.

please help me out.

[SPR]

Hello SSS,

This is a solution:
[pre]
data i;
input id$ date:date9.;
FORMAT DATE DATE9.;
datalines;
a 15aug2007
a 21apr2009
b 05may2009
c 15may2009
d 01feb2010
d 01mar2010
;
run;
proc sort data=i;
by id date;
run;
data r;
retain fd;
set i;
if FIRST.ID then fd=date;
if LAST.ID then TBF=date-fd;
if FIRST.ID and LAST.ID then TBF=.;
by ID;
drop fd;
run;
[/pre]
Sincerely,
SPR

[sss]

Hey buddy
thnxs a lot it works

[SPR]

I am a bit surprised why do you need all those TBF=. ? May be better exclude them?

SPR

[sss]

HI SPR,

I am calculating TBF for asset.
how frequently it has failed and and the time.

u can see for Asset A: it has failed for 2 time . so, i wnt 2 calculate the TBF for the asset
Asset B: its only 1 time and so on

Hey can we do same task using proc sql ? if possble plz suggest me


thnx
shivkumar

[SPR]

Hello SSS,

This is a solution:
[pre]
proc SQL;
create table t as
select id, min(date) as min, max(date) as max
from i
group by ID
;quit;
proc SQL;
create table r as
select a.id, a.date,
case
when date=min or max-min=0 then .
else max-min
end as TBF
from i as a left join t as b
on a.ID=b.ID
order by ID, date
;quit;
[/pre]
Sincerely,
SPR