topic Re: similar observations sequence and repeat time.How to gain? in SAS Programming

similar observations sequence and repeat time.How to gain?

deleted_user — Fri, 04 Jun 2010 15:23:36 GMT

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...

Re: similar observations sequence and repeat time.How to gain?

deleted_user — Fri, 04 Jun 2010 15:37:58 GMT

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

Re: similar observations sequence and repeat time.How to gain?

Ksharp — Sun, 06 Jun 2010 03:41:33 GMT

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

Re: similar observations sequence and repeat time.How to gain?

Ksharp — Sun, 06 Jun 2010 03:43:55 GMT

Re: similar observations sequence and repeat time.How to gain?

Patrick — Sun, 06 Jun 2010 03:52:32 GMT

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

Re: similar observations sequence and repeat time.How to gain?

deleted_user — Sun, 06 Jun 2010 14:18:35 GMT

thank u very much!!

i will try to check it.

Re: similar observations sequence and repeat time.How to gain?

deleted_user — Mon, 07 Jun 2010 09:09:51 GMT

Ksharp:
I tried. Yes, it works.

Thank you very much. 🙂

Re: similar observations sequence and repeat time.How to gain?

deleted_user — Tue, 08 Jun 2010 16:00:22 GMT

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.:)