topic create new data set from arithmatical operation between observations in SAS Procedures

create new data set from arithmatical operation between observations

deleted_user — Thu, 30 Oct 2008 11:20:08 GMT

Hi all,

I am newbie to SAS with some experience with other computer programming language. I have problem as given below.

Data A (have been sorted by Vehicle_Number Entry_Date):
* Vehichle_Number
* Entry_Date
* Gate_ID

From this data A, I want to have data B with:
* Vehicle Number
* Run_Time
* Gate_From
* Gate_To

if Vehicle_Number (i) = Vehicle_Number (i+1) then
do
Vehicle_Number = Vehicle_Number
Run_Time = Entry_Date (i+1) - Entry_Date (i) --> between two consecutive obs
Gate_From = Gate_ID (i)
Gate_To = Gate_ID (i+1)
end;

I would like to translate that script into SAS programming language. How can I do that?

Any advice will help.

Thanks

Re: create new data set from arithmatical operation between observations

LinusH — Thu, 30 Oct 2008 11:31:47 GMT

If you could supply some sample input data and desirable output, it would easier to help.

/Linus

Re: create new data set from arithmatical operation between observations

deleted_user — Fri, 31 Oct 2008 01:46:30 GMT

Hi Linus,

Thanks for your response.

Below is the sample:

Obs Vehicle_Number Entry_Date_Time Gate_ID
1 SGG9274A 12-Jan-07 02:18:27 AM A
2 SSC7789N 16-May-07 04:45:58 PM B
3 SSC7989N 16-May-07 11:00:29 PM B
4 SSC7989N 05-Jul-07 06:33:38 PM B
5 SSC7989N 18-Jul-07 12:22:53 PM C
6 STM1209B 28-Feb-07 05:07:31 AM A
7 SVX3120C 06-Sep-07 09:29:20 AM A
8 SVX3120C 21-Sep-07 07:23:48 AM C
9 SVX3120C 21-Sep-07 07:55:28 AM C
10 SVY3542N 09-Mar-07 03:06:24 PM B
11 SVY3542N 19-Mar-07 12:21:02 AM B

And here is the desirable output:

Obs Vehicle_Number Run_Time Gate_From Gate_To
1 SSC7789N 22471 B B
2 SSC7989N 1625589 B B
3 SSC7989N 1100955 B C
4 SVX3120C 1288468 A C
5 SVX3120C 1900 C C
6 SVY3542N 810878 B B

Run_Time is in seconds.

Thanks.

Re: create new data set from arithmatical operation between observations

data_null__ — Fri, 31 Oct 2008 20:25:08 GMT

I think this is what you want but my results do not match your expectation exactly. You do this by looking ahead by usually "I think" it is done by looking back.

[pre]
dm 'clear log; clear output;';
data cars;
input
Obs VehicleNumber :$8. EntryDateTime & datetime. GateID:$1.;
format E: dateampm19.;
drop obs;
cards;
1 SGG9274A 12-Jan-07 02:18:27 AM A
2 SSC7789N 16-May-07 04:45:58 PM B
3 SSC7989N 16-May-07 11:00:29 PM B
4 SSC7989N 05-Jul-07 06:33:38 PM B
5 SSC7989N 18-Jul-07 12:22:53 PM C
6 STM1209B 28-Feb-07 05:07:31 AM A
7 SVX3120C 06-Sep-07 09:29:20 AM A
8 SVX3120C 21-Sep-07 07:23:48 AM C
9 SVX3120C 21-Sep-07 07:55:28 AM C
10 SVY3542N 09-Mar-07 03:06:24 PM B
11 SVY3542N 19-Mar-07 12:21:02 AM B
;;;;
run;
proc sort;
by V: E:;
run;
data cars;
do until(last.VehicleNumber);
set cars;
by v: E:;
FromGate = lag(GateID);
RunTime = dif(EntryDateTime);
ToGate = GateID;
if first.VehicleNumber then call missing(of FromGate RunTime);
if not first.VehicleNumber then output;
end;
format runTime comma12.;
drop Entry: GateID;
drop
run;
proc print;
by v:;
id v:;
run;

Vehicle From To
Number Gate RunTime Gate

SSC7989N B 4,303,989 B
B 1,100,955 C

SVX3120C A 1,288,468 C
C 1,900 C

SVY3542N B 810,878 B

[pre]

Re: create new data set from arithmatical operation between observations

deleted_user — Mon, 03 Nov 2008 01:15:27 GMT

Thanks for the idea. I learn something from your script.
I will have SAS Base training this week and hope will find more.