Creating Flight Segment Index

[BCNAV]

I have lots of data as follows:

FlightID	Entry	Exit	NM	FIR_ID	FIR
94832841	0	165	35	1074	EDMONTON
94832841	165	290	65	1074	EDMONTON
94832841	290	330	31	1074	EDMONTON
94832841	330	330	256	1079	WINNIPEG
94832841	330	330	90	1079	WINNIPEG
94832841	330	350	167	1079	WINNIPEG
94832841	350	350	241	1079	WINNIPEG
94832841	350	350	198	1079	WINNIPEG
94832841	350	350	14	1078	TORONTO
94832841	350	330	207	1078	TORONTO
94832841	330	280	80	1078	TORONTO
94832841	280	132	48	1078	TORONTO
94832841	132	0	26	1078	TORONTO

Every flight has a unique FlightID and each flight can pass through regions (FIR). I'd like to be able to create a flight_segment variable.

Every time a NEW FlightID is found, that would be the start of a flight and thus the start of a segment...that is flight_segment would equal 1. If the next FlightID is the same AND the FIR is the same, it would remain at 1. If the next FlightID is the SAME and the FIR has changed, then the flight_segment would have one added to it.

Any ideas?

Thx

[ballardw · Accepted solution]

Post example data in the form of a data step.

Not tested as no data provided:

data want;
   set have;
   by notsorted flightid notsorted FIR;
   if first.flightid then segment=0;
   if first.fir then segment+1;
run;

[AMSAS]

You can use SQL to do it

data have ;
	input flightID $ entry $ exit $ nm $ fir_id $ fir $ ;
cards ;
94832841	0	165	35	1074	EDMONTON
94832841	165	290	65	1074	EDMONTON
94832841	290	330	31	1074	EDMONTON
94832841	330	330	256	1079	WINNIPEG
94832841	330	330	90	1079	WINNIPEG
94832841	330	350	167	1079	WINNIPEG
94832841	350	350	241	1079	WINNIPEG
94832841	350	350	198	1079	WINNIPEG
94832841	350	350	14	1078	TORONTO
94832841	350	330	207	1078	TORONTO
94832841	330	280	80	1078	TORONTO
94832841	280	132	48	1078	TORONTO
94832841	132	0	26	1078	TORONTO
94832842	0	165	35	1074	EDMONTON
94832842	165	290	65	1074	EDMONTON
94832842	290	330	31	1074	EDMONTON
94832842	330	330	256	1079	WINNIPEG
94832842	330	330	90	1079	WINNIPEG
94832842	330	350	167	1079	WINNIPEG
94832842	350	350	241	1079	WINNIPEG
94832842	350	350	198	1079	WINNIPEG
;

proc sql ;
	select flightid, count(unique(fir))
	from have
	group by flightid 
	
	;
quit ;