similar observations sequence and repeat time.How to gain?

[deleted_user]

I got a big data set, which has the form of
say in work.a;

















where Add and T are the variable name. and "A".... are the observations;

who can told me how to write a code to generate the belowing table?









Thank you very much...AND hope someone can help me, as i am quite not good at SAS...

[deleted_user]

you can sort the dataset and use retain function to calculate new columns.

[Ksharp]

Hi. The code is a little long. But I got it.


[pre]
data area;
input area : $1. time : mmddyy10. ;
format time mmddyy10.;
datalines;
A 2/1/2007
A 10/11/2008
A 4/21/2009
B 7/2/2008
C 1/21/2008
C 9/12/2009
;
run;
*to get repeated sequence duration1 duration2;
data temp;
set area;
by area;
retain base repeated 0;
if first.area then do;
repeated=not(repeated);
sequence=0;
base=time;
end;
duration1=dif(time);
sequence+1;
duration2=time-base;
drop base;
run;
*to adjust duration1;
data tmp;
set temp;
by area;
if first.area then duration1=0;
run;
*to get duration3 is somewhat complex;
data index;
set tmp;
by area;
if first.area then delete;
keep area time;
run;
data merged;
merge tmp index(rename=(time=t));
by area;
run;
proc format;
value fmt
0,. = 'Truncated';
run;
data result;
set merged;
duration3= t - time;
format duration3 fmt.;
drop t;
run;
proc print noobs;
run;
[/pre]


Ksharp

[Ksharp]

Hi. The code is a little long. But I got it.


[pre]
data area;
input area : $1. time : mmddyy10. ;
format time mmddyy10.;
datalines;
A 2/1/2007
A 10/11/2008
A 4/21/2009
B 7/2/2008
C 1/21/2008
C 9/12/2009
;
run;
*to get repeated sequence duration1 duration2;
data temp;
set area;
by area;
retain base repeated 0;
if first.area then do;
repeated=not(repeated);
sequence=0;
base=time;
end;
duration1=dif(time);
sequence+1;
duration2=time-base;
drop base;
run;
*to adjust duration1;
data tmp;
set temp;
by area;
if first.area then duration1=0;
run;
*to get duration3 is somewhat complex;
data index;
set tmp;
by area;
if first.area then delete;
keep area time;
run;
data merged;
merge tmp index(rename=(time=t));
by area;
run;
proc format;
value fmt
0,. = 'Truncated';
run;
data result;
set merged;
duration3= t - time;
format duration3 fmt.;
drop t;
run;
proc print noobs;
run;
[/pre]


Ksharp

[deleted_user]

Ksharp:
I tried. Yes, it works.

Thank you very much. 🙂

[Patrick]

Sort by Address and Time, then do something like this (untested code).

data inter / view=inter;
set..
by address;
retain startTime;
rowcount=_n_;
lagTime=lag(time).
repeated=1;

if first.address then
do;
sequence=0;
lagTime=time;
startTime=time;
if last.address then repeated=0;
end;

sequence+1;
duration1=lagTime-time;
duration2=time-startTime;
run;


proc sql;
create table want as
select l.*,r.duration1 as duration3
from inter as l left join inter as r
on l.rowcount=(r.rowcount+1) and l.address=r.address;
quit;


HTH
Patrick Message was edited by: Patrick

[deleted_user]

thank u very much!!

i will try to check it.

[deleted_user]

the final work, i use is based on ksharp's answer, however, with some function usage , like lags, suggested by Patrick. and get the work done.

Come to say thank you both two again.:)