BookmarkSubscribeRSS Feed
deleted_user
Not applicable
I got a big data set, which has the form of
say in work.a;







SMALLforSAS









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?



2





Thank you very much...AND hope someone can help me, as i am quite not good at SAS...
7 REPLIES 7
deleted_user
Not applicable
you can sort the dataset and use retain function to calculate new columns.
Ksharp
Super User
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
Super User
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
Not applicable
Ksharp:
I tried. Yes, it works.

Thank you very much. 🙂
Patrick
Opal | Level 21
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
Not applicable
thank u very much!!

i will try to check it.
deleted_user
Not applicable
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.:)

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 907 views
  • 0 likes
  • 3 in conversation