Pivoting data

Reply
New Contributor
Posts: 4

Pivoting data

Good morning all,

I’m a SAS newbie and I needed some help to perform some data manipulation steps in SAS.

I have a booking table (126+ million rows) which I am looking to pivot, the table below contain 1 leg per row for each passenger and I’m looking to stitching up the entire trip/journey of each passenger which can make up of 1 or more ‘legs’.

Note that each member id can have multiple bookings for their family members or friends grouped under the same travel_id.

I already have the local arrival and departure (Australia) time for each leg which I need to convert into something like GMT or UTC so that I can determine the order sequence for each of the legs.

My output would be something like DFW:LAX(AA)|LAX:BNE(VA)|BNE:LAX(VA)|LAXSmiley Very HappyFW(AA)|DFW:VPS(AA)

where the acronym in the bracket is the operator.

Secondly I also would like to calculate for each passenger the total duration of their stay in each country as well as the name of the country that they spend the longest time in.

For example the pattern of stay based on the duration between the first off airport and the second board airport, second off airport and third board airport and so on e.g. AU-1-AU-0-US-0-US-21-US-7-US-0-AU-0 would summarise into AU_Duration = 0, US_Duration = 28 thus the country that the passenger spend the most time in is US.

Many Thanks,

Pete

id,Travel_ID,Travel_SEGMENT_ID,Travel_PAXI,IT_NO,Operator,Flight_No,Board_airport,Off_airport,Dep_Dt,Dep_Dttm,Arr_Dttm,BOARD_CTRY_CODE,OFF_CTRY_CODE

747,313431699,5,2,1,VA ,8,DFW ,BNE ,15/08/2013,15/08/2013 22:15,17/08/2013 5:10,US ,AU

747,313431699,11,2,1,VA ,1938,PER ,ASP ,25/08/2013,25/08/2013 12:25,25/08/2013 16:35,AU ,AU

747,313431699,17,2,1,VA ,923,CNS ,SYD ,30/08/2013,30/08/2013 14:20,30/08/2013 17:15,AU ,AU

747,313431699,8,2,1,VA ,589,BNE ,PER ,17/08/2013,17/08/2013 8:20,17/08/2013 12:00,AU ,AU

747,313431699,2,2,1,VA ,4600,IAH ,DFW ,15/08/2013,15/08/2013 18:05,15/08/2013 19:25,US ,US

747,313431699,20,2,1,VA ,7,SYD ,DFW ,1/09/2013,1/09/2013 13:25,1/09/2013 13:45,AU ,US

747,313431699,26,2,1,UA ,4283,DFW ,IAH ,1/09/2013,1/09/2013 17:08,1/09/2013 18:15,US ,US

747,313431699,14,2,1,VA ,1948,ASP ,CNS ,25/08/2013,25/08/2013 17:25,25/08/2013 20:00,AU ,AU

777,313431699,1,1,1,VA ,4600,IAH ,DFW ,15/08/2013,15/08/2013 18:05,15/08/2013 19:25,US ,US

777,313431699,4,1,1,VA ,8,DFW ,BNE ,15/08/2013,15/08/2013 22:15,17/08/2013 5:10,US ,AU

777,313431699,7,1,1,VA ,589,BNE ,PER ,17/08/2013,17/08/2013 8:20,17/08/2013 12:00,AU ,AU

777,313431699,10,1,1,VA ,1938,PER ,ASP ,25/08/2013,25/08/2013 12:25,25/08/2013 16:35,AU ,AU

777,313431699,13,1,1,VA ,1948,ASP ,CNS ,25/08/2013,25/08/2013 17:25,25/08/2013 20:00,AU ,AU

777,313431699,16,1,1,VA ,923,CNS ,SYD ,30/08/2013,30/08/2013 14:20,30/08/2013 17:15,AU ,AU

777,313431699,19,1,1,VA ,7,SYD ,DFW ,1/09/2013,1/09/2013 13:25,1/09/2013 13:45,AU ,US

777,313431699,25,1,1,UA ,4283,DFW ,IAH ,1/09/2013,1/09/2013 17:08,1/09/2013 18:15,US ,US

787,313431699,35,4,2,UA ,4283,DFW ,IAH ,1/09/2013,1/09/2013 17:08,1/09/2013 18:15,US ,US

787,313431699,37,4,2,VA ,8,DFW ,BNE ,17/08/2013,17/08/2013 22:15,19/08/2013 5:10,US ,AU

787,313431699,36,4,2,VA ,4600,IAH ,DFW ,17/08/2013,17/08/2013 18:05,17/08/2013 19:25,US ,US

787,313431699,32,4,2,VA ,1948,ASP ,CNS ,25/08/2013,25/08/2013 17:25,25/08/2013 20:00,AU ,AU

787,313431699,33,4,2,VA ,923,CNS ,SYD ,30/08/2013,30/08/2013 14:20,30/08/2013 17:15,AU ,AU

787,313431699,31,4,2,VA ,1938,PER ,ASP ,25/08/2013,25/08/2013 12:25,25/08/2013 16:35,AU ,AU

787,313431699,38,4,2,VA ,589,BNE ,PER ,19/08/2013,19/08/2013 8:20,19/08/2013 12:00,AU ,AU

787,313431699,34,4,2,VA ,7,SYD ,DFW ,1/09/2013,1/09/2013 13:25,1/09/2013 13:45,AU ,US

737,333189400,68,7,2,VA ,969,TSV ,BNE ,21/06/2014,21/06/2014 5:55,21/06/2014 7:40,AU ,AU

737,333189400,84,7,2,VA ,517,BNE ,SYD ,21/06/2014,21/06/2014 10:25,21/06/2014 12:00,AU ,AU

737,333189400,86,7,2,VA ,7,SYD ,DFW ,21/06/2014,21/06/2014 13:20,21/06/2014 13:45,AU ,US

737,333189400,88,7,2,VA ,4582,DFW ,MCO ,21/06/2014,21/06/2014 17:10,21/06/2014 20:45,US ,US

737,333189400,72,7,2,VA ,4370,FLL ,DFW ,12/07/2014,12/07/2014 16:45,12/07/2014 18:50,US ,US

737,333189400,74,7,2,VA ,8,DFW ,BNE ,12/07/2014,12/07/2014 21:55,14/07/2014 5:00,US ,AU

737,333189400,97,7,2,VA ,970,BNE ,TSV ,14/07/2014,14/07/2014 8:55,14/07/2014 10:55,AU ,AU

737,303602975,2,2,1,VA ,969,TSV ,BNE ,1/04/2013,1/04/2013 5:55,1/04/2013 7:45,AU ,AU

737,303602975,7,2,1,VA ,661,BNE ,ADL ,1/04/2013,1/04/2013 8:25,1/04/2013 11:35,AU ,AU

737,303602975,12,2,1,VA ,660,ADL ,BNE ,12/04/2013,12/04/2013 6:15,12/04/2013 9:05,AU ,AU

737,303602975,17,2,1,VA ,972,BNE ,TSV ,12/04/2013,12/04/2013 11:50,12/04/2013 13:50,AU ,AU

737,303616665,1,1,1,VA ,977,TSV ,BNE ,23/12/2012,23/12/2012 19:35,23/12/2012 21:25,AU ,AU

737,303616665,5,1,1,EK ,433,BNE ,DXB ,24/12/2012,24/12/2012 2:35,24/12/2012 13:00,AU ,AE

737,303616665,7,1,1,EK ,538,DXB ,AMD ,24/12/2012,24/12/2012 23:00,25/12/2012 3:00,AE ,IN

737,303616665,11,1,1,EK ,434,DXB ,BNE ,25/01/2013,25/01/2013 10:25,26/01/2013 6:40,AE ,AU

737,303616665,13,1,1,VA ,974,BNE ,TSV ,26/01/2013,26/01/2013 13:55,26/01/2013 15:50,AU ,AU

737,296506821,1,1,1,VA ,2318,TSV ,CNS ,6/07/2012,6/07/2012 20:10,6/07/2012 21:00,AU ,AU

737,298619570,7,2,1,VA ,513,BNE ,SYD ,12/10/2012,12/10/2012 8:25,12/10/2012 11:00,AU ,AU

737,298619570,12,2,1,VA ,19,SYD ,BNE ,25/10/2012,25/10/2012 11:30,25/10/2012 12:10,AU ,AU

737,298619570,17,2,1,VA ,974,BNE ,TSV ,25/10/2012,25/10/2012 13:55,25/10/2012 15:55,AU ,AU

737,298619570,2,2,1,VA ,969,TSV ,BNE ,12/10/2012,12/10/2012 5:55,12/10/2012 7:40,AU ,AU

737,287279108,81,1,1,UA ,1591,MCO ,IAH ,10/04/2012,10/04/2012 12:58,10/04/2012 14:26,US ,US

737,287279108,5,1,1,VA ,521,BNE ,SYD ,19/03/2012,19/03/2012 10:25,19/03/2012 13:00,AU ,AU

737,287279108,9,1,1,VA ,7,SYD ,DFW ,19/03/2012,19/03/2012 14:40,19/03/2012 13:50,AU ,US

737,287279108,13,1,1,VA ,4666,DFW ,MCO ,19/03/2012,19/03/2012 15:50,19/03/2012 19:10,US ,US

737,287279108,17,1,1,VA ,4507,IAH ,DFW ,16/04/2012,16/04/2012 19:50,16/04/2012 21:05,US ,US

737,287279108,21,1,1,VA ,8,DFW ,BNE ,16/04/2012,16/04/2012 21:55,18/04/2012 5:00,US ,AU

737,287279108,53,1,1,VA ,970,BNE ,TSV ,18/04/2012,18/04/2012 8:55,18/04/2012 10:55,AU ,AU

737,287279108,1,1,1,VA ,969,TSV ,BNE ,19/03/2012,19/03/2012 5:55,19/03/2012 7:45,AU ,AU

737,296512839,2,2,1,VA ,2301,CNS ,TSV ,8/07/2012,8/07/2012 6:45,8/07/2012 7:40,AU ,AU

737,251658591,6,6,1,VA ,977,TSV ,BNE ,18/06/2010,18/06/2010 19:35,18/06/2010 21:15,AU ,AU

737,251658591,15,6,1,VA ,978,BNE ,TSV ,26/06/2010,26/06/2010 19:35,26/06/2010 21:35,AU ,AU

737,256408545,7,2,1,VA ,15,BNE ,LAX ,2/10/2010,2/10/2010 11:00,2/10/2010 7:00,AU ,US

737,256408545,2,2,1,VA ,969,TSV ,BNE ,2/10/2010,2/10/2010 5:55,2/10/2010 7:35,AU ,AU

737,256408545,47,2,1,UA ,527,LAX ,MCO ,2/10/2010,2/10/2010 11:30,2/10/2010 19:23,US ,US

737,256408545,27,2,1,UA ,277,MCO ,LAX ,23/10/2010,23/10/2010 19:10,23/10/2010 21:34,US ,US

737,256408545,12,2,1,VA ,16,LAX ,BNE ,23/10/2010,23/10/2010 23:30,25/10/2010 6:20,US ,AU

737,256408545,17,2,1,VA ,970,BNE ,TSV ,25/10/2010,25/10/2010 8:55,25/10/2010 10:55,AU ,AU

737,230923370,3,3,1,VA ,969,TSV ,BNE ,26/06/2009,26/06/2009 5:55,26/06/2009 7:35,AU ,AU

737,230923370,18,3,1,VA ,970,BNE ,TSV ,23/07/2009,23/07/2009 8:55,23/07/2009 10:55,AU ,AU

737,228713305,3,3,1,VA ,972,BNE ,TSV ,25/01/2009,25/01/2009 10:55,25/01/2009 12:55,AU ,AU

737,240349038,2,2,1,VA ,974,BNE ,TSV ,15/10/2009,15/10/2009 14:00,15/10/2009 16:00,AU ,AU

737,240348477,3,3,1,VA ,975,TSV ,BNE ,21/09/2009,21/09/2009 16:40,21/09/2009 18:20,AU ,AU

737,229797259,2,2,1,VA ,15,BNE ,LAX ,26/06/2009,26/06/2009 11:05,26/06/2009 7:00,AU ,US

737,229797259,6,2,1,VA ,107,LAX ,JFK ,26/06/2009,26/06/2009 9:15,26/06/2009 17:30,US ,US

737,229797259,10,2,1,VA ,108,JFK ,LAX ,21/07/2009,21/07/2009 19:10,21/07/2009 22:10,US ,US

737,229797259,14,2,1,VA ,16,LAX ,BNE ,21/07/2009,21/07/2009 23:20,23/07/2009 6:10,US ,AU

737,224779073,1,1,1,VA ,977,TSV ,BNE ,23/12/2008,23/12/2008 19:25,23/12/2008 21:10,AU ,AU

737,348281505,9,3,1,VA ,513,BNE ,SYD ,6/01/2015,6/01/2015 8:25,6/01/2015 11:00,AU ,AU

737,348281505,3,3,1,VA ,969,TSV ,BNE ,6/01/2015,6/01/2015 5:55,6/01/2015 7:45,AU ,AU

737,321639598,2,2,1,AA ,1147,TPA ,DFW ,22/09/2013,22/09/2013 8:10,22/09/2013 9:50,US ,US

737,319035028,2,2,1,VA ,8,DFW ,BNE ,22/09/2013,22/09/2013 22:15,24/09/2013 5:10,US ,AU

737,319035028,12,2,1,VA ,970,BNE ,TSV ,24/09/2013,24/09/2013 8:55,24/09/2013 10:55,AU ,AU

737,319172037,12,2,1,AA ,63,CDG ,MIA ,11/09/2013,11/09/2013 10:00,11/09/2013 14:10,FR ,US

737,319172037,17,2,1,AA ,262,MIA ,MCO ,11/09/2013,11/09/2013 16:25,11/09/2013 17:30,US ,US

737,312726327,2,2,1,VA ,977,TSV ,BNE ,21/08/2013,21/08/2013 19:40,21/08/2013 21:25,AU ,AU

737,312726327,7,2,1,EK ,433,BNE ,DXB ,21/08/2013,21/08/2013 23:30,22/08/2013 10:00,AU ,AE

737,312726327,12,2,1,EK ,143,DXB ,MAD ,22/08/2013,22/08/2013 14:25,22/08/2013 20:20,AE ,ES

767,321639598,3,3,1,AA ,1147,TPA ,DFW ,22/09/2013,22/09/2013 8:10,22/09/2013 9:50,US ,US

767,319035028,3,3,1,VA ,8,DFW ,BNE ,22/09/2013,22/09/2013 22:15,24/09/2013 5:10,US ,AU

767,319035028,13,3,1,VA ,970,BNE ,TSV ,24/09/2013,24/09/2013 8:55,24/09/2013 10:55,AU ,AU

767,319172037,13,3,1,AA ,63,CDG ,MIA ,11/09/2013,11/09/2013 10:00,11/09/2013 14:10,FR ,US

767,319172037,18,3,1,AA ,262,MIA ,MCO ,11/09/2013,11/09/2013 16:25,11/09/2013 17:30,US ,US

767,312726327,3,3,1,VA ,977,TSV ,BNE ,21/08/2013,21/08/2013 19:40,21/08/2013 21:25,AU ,AU

767,312726327,8,3,1,EK ,433,BNE ,DXB ,21/08/2013,21/08/2013 23:30,22/08/2013 10:00,AU ,AE

767,312726327,13,3,1,EK ,143,DXB ,MAD ,22/08/2013,22/08/2013 14:25,22/08/2013 20:20,AE ,ES

767,229797259,4,4,1,VA ,15,BNE ,LAX ,26/06/2009,26/06/2009 11:05,26/06/2009 7:00,AU ,US

767,229797259,8,4,1,VA ,107,LAX ,JFK ,26/06/2009,26/06/2009 9:15,26/06/2009 17:30,US ,US

767,229797259,12,4,1,VA ,108,JFK ,LAX ,21/07/2009,21/07/2009 19:10,21/07/2009 22:10,US ,US

767,229797259,16,4,1,VA ,16,LAX ,BNE ,21/07/2009,21/07/2009 23:20,23/07/2009 6:10,US ,AU

767,228713305,4,4,1,VA ,972,BNE ,TSV ,25/01/2009,25/01/2009 10:55,25/01/2009 12:55,AU ,AU

767,224779073,4,4,1,VA ,977,TSV ,BNE ,23/12/2008,23/12/2008 19:25,23/12/2008 21:10,AU ,AU

767,240348477,4,4,1,VA ,975,TSV ,BNE ,21/09/2009,21/09/2009 16:40,21/09/2009 18:20,AU ,AU

767,240349038,3,3,1,VA ,974,BNE ,TSV ,15/10/2009,15/10/2009 14:00,15/10/2009 16:00,AU ,AU

767,230923370,4,4,1,VA ,969,TSV ,BNE ,26/06/2009,26/06/2009 5:55,26/06/2009 7:35,AU ,AU

767,230923370,19,4,1,VA ,970,BNE ,TSV ,23/07/2009,23/07/2009 8:55,23/07/2009 10:55,AU ,AU

767,256408545,3,3,1,VA ,969,TSV ,BNE ,2/10/2010,2/10/2010 5:55,2/10/2010 7:35,AU ,AU

767,256408545,8,3,1,VA ,15,BNE ,LAX ,2/10/2010,2/10/2010 11:00,2/10/2010 7:00,AU ,US

767,256408545,48,3,1,UA ,527,LAX ,MCO ,2/10/2010,2/10/2010 11:30,2/10/2010 19:23,US ,US

767,256408545,28,3,1,UA ,277,MCO ,LAX ,23/10/2010,23/10/2010 19:10,23/10/2010 21:34,US ,US

767,256408545,13,3,1,VA ,16,LAX ,BNE ,23/10/2010,23/10/2010 23:30,25/10/2010 6:20,US ,AU

767,256408545,18,3,1,VA ,970,BNE ,TSV ,25/10/2010,25/10/2010 8:55,25/10/2010 10:55,AU ,AU

767,251658591,7,7,1,VA ,977,TSV ,BNE ,18/06/2010,18/06/2010 19:35,18/06/2010 21:15,AU ,AU

767,251658591,16,7,1,VA ,978,BNE ,TSV ,26/06/2010,26/06/2010 19:35,26/06/2010 21:35,AU ,AU

767,287279108,2,2,1,VA ,969,TSV ,BNE ,19/03/2012,19/03/2012 5:55,19/03/2012 7:45,AU ,AU

767,287279108,6,2,1,VA ,521,BNE ,SYD ,19/03/2012,19/03/2012 10:25,19/03/2012 13:00,AU ,AU

767,287279108,10,2,1,VA ,7,SYD ,DFW ,19/03/2012,19/03/2012 14:40,19/03/2012 13:50,AU ,US

767,287279108,14,2,1,VA ,4666,DFW ,MCO ,19/03/2012,19/03/2012 15:50,19/03/2012 19:10,US ,US

767,287279108,82,2,1,UA ,1591,MCO ,IAH ,10/04/2012,10/04/2012 12:58,10/04/2012 14:26,US ,US

767,287279108,18,2,1,VA ,4507,IAH ,DFW ,16/04/2012,16/04/2012 19:50,16/04/2012 21:05,US ,US

767,287279108,22,2,1,VA ,8,DFW ,BNE ,16/04/2012,16/04/2012 21:55,18/04/2012 5:00,US ,AU

767,287279108,54,2,1,VA ,970,BNE ,TSV ,18/04/2012,18/04/2012 8:55,18/04/2012 10:55,AU ,AU

767,303602975,3,3,1,VA ,969,TSV ,BNE ,1/04/2013,1/04/2013 5:55,1/04/2013 7:45,AU ,AU

767,303602975,8,3,1,VA ,661,BNE ,ADL ,1/04/2013,1/04/2013 8:25,1/04/2013 11:35,AU ,AU

767,303602975,13,3,1,VA ,660,ADL ,BNE ,12/04/2013,12/04/2013 6:15,12/04/2013 9:05,AU ,AU

767,303602975,18,3,1,VA ,972,BNE ,TSV ,12/04/2013,12/04/2013 11:50,12/04/2013 13:50,AU ,AU

767,303616665,2,2,1,VA ,977,TSV ,BNE ,23/12/2012,23/12/2012 19:35,23/12/2012 21:25,AU ,AU

767,303616665,6,2,1,EK ,433,BNE ,DXB ,24/12/2012,24/12/2012 2:35,24/12/2012 13:00,AU ,AE

767,303616665,8,2,1,EK ,538,DXB ,AMD ,24/12/2012,24/12/2012 23:00,25/12/2012 3:00,AE ,IN

767,303616665,12,2,1,EK ,434,DXB ,BNE ,25/01/2013,25/01/2013 10:25,26/01/2013 6:40,AE ,AU

767,303616665,14,2,1,VA ,974,BNE ,TSV ,26/01/2013,26/01/2013 13:55,26/01/2013 15:50,AU ,AU

767,298619570,5,5,1,VA ,969,TSV ,BNE ,12/10/2012,12/10/2012 5:55,12/10/2012 7:40,AU ,AU

767,298619570,10,5,1,VA ,513,BNE ,SYD ,12/10/2012,12/10/2012 8:25,12/10/2012 11:00,AU ,AU

767,298619570,15,5,1,VA ,19,SYD ,BNE ,25/10/2012,25/10/2012 11:30,25/10/2012 12:10,AU ,AU

767,298619570,20,5,1,VA ,974,BNE ,TSV ,25/10/2012,25/10/2012 13:55,25/10/2012 15:55,AU ,AU

767,348281505,4,4,1,VA ,969,TSV ,BNE ,6/01/2015,6/01/2015 5:55,6/01/2015 7:45,AU ,AU

767,348281505,10,4,1,VA ,513,BNE ,SYD ,6/01/2015,6/01/2015 8:25,6/01/2015 11:00,AU ,AU

767,333189400,55,3,1,VA ,969,TSV ,BNE ,21/06/2014,21/06/2014 5:55,21/06/2014 7:40,AU ,AU

767,333189400,58,3,1,VA ,517,BNE ,SYD ,21/06/2014,21/06/2014 10:25,21/06/2014 12:00,AU ,AU

767,333189400,61,3,1,VA ,7,SYD ,DFW ,21/06/2014,21/06/2014 13:20,21/06/2014 13:45,AU ,US

767,333189400,52,3,1,VA ,4582,DFW ,MCO ,21/06/2014,21/06/2014 17:10,21/06/2014 20:45,US ,US

767,333189400,18,3,1,VA ,4370,FLL ,DFW ,12/07/2014,12/07/2014 16:45,12/07/2014 18:50,US ,US

767,333189400,23,3,1,VA ,8,DFW ,BNE ,12/07/2014,12/07/2014 21:55,14/07/2014 5:00,US ,AU

767,333189400,93,3,1,VA ,970,BNE ,TSV ,14/07/2014,14/07/2014 8:55,14/07/2014 10:55,AU ,AU

717,312726327,14,4,1,EK ,143,DXB ,MAD ,22/08/2013,22/08/2013 14:25,22/08/2013 20:20,AE ,ES

717,312726327,9,4,1,EK ,433,BNE ,DXB ,21/08/2013,21/08/2013 23:30,22/08/2013 10:00,AU ,AE

717,312726327,4,4,1,VA ,977,TSV ,BNE ,21/08/2013,21/08/2013 19:40,21/08/2013 21:25,AU ,AU

717,319035028,14,4,1,VA ,970,BNE ,TSV ,24/09/2013,24/09/2013 8:55,24/09/2013 10:55,AU ,AU

717,319035028,4,4,1,VA ,8,DFW ,BNE ,22/09/2013,22/09/2013 22:15,24/09/2013 5:10,US ,AU

717,319172037,19,4,1,AA ,262,MIA ,MCO ,11/09/2013,11/09/2013 16:25,11/09/2013 17:30,US ,US

717,319172037,14,4,1,AA ,63,CDG ,MIA ,11/09/2013,11/09/2013 10:00,11/09/2013 14:10,FR ,US

717,321639598,4,4,1,AA ,1147,TPA ,DFW ,22/09/2013,22/09/2013 8:10,22/09/2013 9:50,US ,US

717,333189400,59,4,1,VA ,517,BNE ,SYD ,21/06/2014,21/06/2014 10:25,21/06/2014 12:00,AU ,AU

717,333189400,56,4,1,VA ,969,TSV ,BNE ,21/06/2014,21/06/2014 5:55,21/06/2014 7:40,AU ,AU

717,333189400,19,4,1,VA ,4370,FLL ,DFW ,12/07/2014,12/07/2014 16:45,12/07/2014 18:50,US ,US

717,333189400,24,4,1,VA ,8,DFW ,BNE ,12/07/2014,12/07/2014 21:55,14/07/2014 5:00,US ,AU

717,333189400,62,4,1,VA ,7,SYD ,DFW ,21/06/2014,21/06/2014 13:20,21/06/2014 13:45,AU ,US

717,333189400,94,4,1,VA ,970,BNE ,TSV ,14/07/2014,14/07/2014 8:55,14/07/2014 10:55,AU ,AU

717,333189400,53,4,1,VA ,4582,DFW ,MCO ,21/06/2014,21/06/2014 17:10,21/06/2014 20:45,US ,US

717,341780674,8,4,1,VA ,9094,QZW ,QZY ,4/09/2015,4/09/2015 9:00,4/09/2015 12:00,FR ,FR

717,277486457,1,1,1,VA ,969,TSV ,BNE ,11/08/2011,11/08/2011 5:55,11/08/2011 7:40,AU ,AU

717,277486457,5,1,1,US ,569,LAX ,MCO ,11/08/2011,11/08/2011 9:50,11/08/2011 19:32,US ,US

717,277486457,8,1,1,VA ,16,LAX ,BNE ,18/08/2011,18/08/2011 23:20,20/08/2011 6:10,US ,AU

717,277486457,9,1,1,VA ,970,BNE ,TSV ,20/08/2011,20/08/2011 8:55,20/08/2011 10:55,AU ,AU

717,277486457,2,1,1,VA ,15,BNE ,LAX ,11/08/2011,11/08/2011 10:55,11/08/2011 7:00,AU ,US

717,277486457,10,1,1,UA ,277,MCO ,LAX ,18/08/2011,18/08/2011 19:15,18/08/2011 21:25,US ,US

717,303616351,14,2,1,EK ,434,DXB ,BNE ,25/01/2013,25/01/2013 10:25,26/01/2013 6:40,AE ,AU

717,303616351,17,2,1,VA ,974,BNE ,TSV ,26/01/2013,26/01/2013 13:55,26/01/2013 15:50,AU ,AU

717,303616351,11,2,1,EK ,538,DXB ,AMD ,24/12/2012,24/12/2012 23:00,25/12/2012 3:00,AE ,IN

717,303616351,8,2,1,EK ,433,BNE ,DXB ,24/12/2012,24/12/2012 2:35,24/12/2012 13:00,AU ,AE

717,303616351,2,2,1,VA ,977,TSV ,BNE ,23/12/2012,23/12/2012 19:35,23/12/2012 21:25,AU ,AU

717,303602975,14,4,1,VA ,660,ADL ,BNE ,12/04/2013,12/04/2013 6:15,12/04/2013 9:05,AU ,AU

717,303602975,19,4,1,VA ,972,BNE ,TSV ,12/04/2013,12/04/2013 11:50,12/04/2013 13:50,AU ,AU

717,303602975,9,4,1,VA ,661,BNE ,ADL ,1/04/2013,1/04/2013 8:25,1/04/2013 11:35,AU ,AU

717,303602975,4,4,1,VA ,969,TSV ,BNE ,1/04/2013,1/04/2013 5:55,1/04/2013 7:45,AU ,AU

717,298619570,4,4,1,VA ,969,TSV ,BNE ,12/10/2012,12/10/2012 5:55,12/10/2012 7:40,AU ,AU

717,298619570,14,4,1,VA ,19,SYD ,BNE ,25/10/2012,25/10/2012 11:30,25/10/2012 12:10,AU ,AU

717,298619570,19,4,1,VA ,974,BNE ,TSV ,25/10/2012,25/10/2012 13:55,25/10/2012 15:55,AU ,AU

717,298619570,9,4,1,VA ,513,BNE ,SYD ,12/10/2012,12/10/2012 8:25,12/10/2012 11:00,AU ,AU

717,287279108,23,3,1,VA ,8,DFW ,BNE ,16/04/2012,16/04/2012 21:55,18/04/2012 5:00,US ,AU

717,287279108,15,3,1,VA ,4666,DFW ,MCO ,19/03/2012,19/03/2012 15:50,19/03/2012 19:10,US ,US

717,287279108,7,3,1,VA ,521,BNE ,SYD ,19/03/2012,19/03/2012 10:25,19/03/2012 13:00,AU ,AU

717,287279108,3,3,1,VA ,969,TSV ,BNE ,19/03/2012,19/03/2012 5:55,19/03/2012 7:45,AU ,AU

717,287279108,11,3,1,VA ,7,SYD ,DFW ,19/03/2012,19/03/2012 14:40,19/03/2012 13:50,AU ,US

717,287279108,83,3,1,UA ,1591,MCO ,IAH ,10/04/2012,10/04/2012 12:58,10/04/2012 14:26,US ,US

717,287279108,55,3,1,VA ,970,BNE ,TSV ,18/04/2012,18/04/2012 8:55,18/04/2012 10:55,AU ,AU

717,287279108,19,3,1,VA ,4507,IAH ,DFW ,16/04/2012,16/04/2012 19:50,16/04/2012 21:05,US ,US

717,256408545,29,4,1,UA ,277,MCO ,LAX ,23/10/2010,23/10/2010 19:10,23/10/2010 21:34,US ,US

717,256408545,9,4,1,VA ,15,BNE ,LAX ,2/10/2010,2/10/2010 11:00,2/10/2010 7:00,AU ,US

717,256408545,4,4,1,VA ,969,TSV ,BNE ,2/10/2010,2/10/2010 5:55,2/10/2010 7:35,AU ,AU

717,256408545,19,4,1,VA ,970,BNE ,TSV ,25/10/2010,25/10/2010 8:55,25/10/2010 10:55,AU ,AU

717,256408545,14,4,1,VA ,16,LAX ,BNE ,23/10/2010,23/10/2010 23:30,25/10/2010 6:20,US ,AU

717,256408545,49,4,1,UA ,527,LAX ,MCO ,2/10/2010,2/10/2010 11:30,2/10/2010 19:23,US ,US

717,251658591,17,8,1,VA ,978,BNE ,TSV ,26/06/2010,26/06/2010 19:35,26/06/2010 21:35,AU ,AU

717,251658591,8,8,1,VA ,977,TSV ,BNE ,18/06/2010,18/06/2010 19:35,18/06/2010 21:15,AU ,AU

717,240348477,5,5,1,VA ,975,TSV ,BNE ,21/09/2009,21/09/2009 16:40,21/09/2009 18:20,AU ,AU

717,240349038,4,4,1,VA ,974,BNE ,TSV ,15/10/2009,15/10/2009 14:00,15/10/2009 16:00,AU ,AU

717,230923370,5,5,1,VA ,969,TSV ,BNE ,26/06/2009,26/06/2009 5:55,26/06/2009 7:35,AU ,AU

717,230923370,20,5,1,VA ,970,BNE ,TSV ,23/07/2009,23/07/2009 8:55,23/07/2009 10:55,AU ,AU

717,228713305,5,5,1,VA ,972,BNE ,TSV ,25/01/2009,25/01/2009 10:55,25/01/2009 12:55,AU ,AU

717,229797259,3,3,1,VA ,15,BNE ,LAX ,26/06/2009,26/06/2009 11:05,26/06/2009 7:00,AU ,US

717,229797259,7,3,1,VA ,107,LAX ,JFK ,26/06/2009,26/06/2009 9:15,26/06/2009 17:30,US ,US

717,229797259,11,3,1,VA ,108,JFK ,LAX ,21/07/2009,21/07/2009 19:10,21/07/2009 22:10,US ,US

717,229797259,15,3,1,VA ,16,LAX ,BNE ,21/07/2009,21/07/2009 23:20,23/07/2009 6:10,US ,AU

717,224779073,5,5,1,VA ,977,TSV ,BNE ,23/12/2008,23/12/2008 19:25,23/12/2008 21:10,AU ,AU

717,348281505,5,5,1,VA ,969,TSV ,BNE ,6/01/2015,6/01/2015 5:55,6/01/2015 7:45,AU ,AU

717,348281505,11,5,1,VA ,513,BNE ,SYD ,6/01/2015,6/01/2015 8:25,6/01/2015 11:00,AU ,AU

757,312726327,15,5,1,EK ,143,DXB ,MAD ,22/08/2013,22/08/2013 14:25,22/08/2013 20:20,AE ,ES

757,312726327,5,5,1,VA ,977,TSV ,BNE ,21/08/2013,21/08/2013 19:40,21/08/2013 21:25,AU ,AU

757,312726327,10,5,1,EK ,433,BNE ,DXB ,21/08/2013,21/08/2013 23:30,22/08/2013 10:00,AU ,AE

757,321639598,5,5,1,AA ,1147,TPA ,DFW ,22/09/2013,22/09/2013 8:10,22/09/2013 9:50,US ,US

757,319172037,15,5,1,AA ,63,CDG ,MIA ,11/09/2013,11/09/2013 10:00,11/09/2013 14:10,FR ,US

757,319035028,5,5,1,VA ,8,DFW ,BNE ,22/09/2013,22/09/2013 22:15,24/09/2013 5:10,US ,AU

757,319035028,15,5,1,VA ,970,BNE ,TSV ,24/09/2013,24/09/2013 8:55,24/09/2013 10:55,AU ,AU

757,319172037,20,5,1,AA ,262,MIA ,MCO ,11/09/2013,11/09/2013 16:25,11/09/2013 17:30,US ,US

757,333189400,20,5,1,VA ,4370,FLL ,DFW ,12/07/2014,12/07/2014 16:45,12/07/2014 18:50,US ,US

757,333189400,95,5,1,VA ,970,BNE ,TSV ,14/07/2014,14/07/2014 8:55,14/07/2014 10:55,AU ,AU

757,333189400,60,5,1,VA ,517,BNE ,SYD ,21/06/2014,21/06/2014 10:25,21/06/2014 12:00,AU ,AU

757,333189400,63,5,1,VA ,7,SYD ,DFW ,21/06/2014,21/06/2014 13:20,21/06/2014 13:45,AU ,US

757,333189400,57,5,1,VA ,969,TSV ,BNE ,21/06/2014,21/06/2014 5:55,21/06/2014 7:40,AU ,AU

757,333189400,25,5,1,VA ,8,DFW ,BNE ,12/07/2014,12/07/2014 21:55,14/07/2014 5:00,US ,AU

757,333189400,54,5,1,VA ,4582,DFW ,MCO ,21/06/2014,21/06/2014 17:10,21/06/2014 20:45,US ,US

757,348281505,12,6,1,VA ,513,BNE ,SYD ,6/01/2015,6/01/2015 8:25,6/01/2015 11:00,AU ,AU

757,348281505,6,6,1,VA ,969,TSV ,BNE ,6/01/2015,6/01/2015 5:55,6/01/2015 7:45,AU ,AU

757,303602975,5,5,1,VA ,969,TSV ,BNE ,1/04/2013,1/04/2013 5:55,1/04/2013 7:45,AU ,AU

757,303602975,20,5,1,VA ,972,BNE ,TSV ,12/04/2013,12/04/2013 11:50,12/04/2013 13:50,AU ,AU

757,303616351,18,3,1,VA ,974,BNE ,TSV ,26/01/2013,26/01/2013 13:55,26/01/2013 15:50,AU ,AU

757,303616351,3,3,1,VA ,977,TSV ,BNE ,23/12/2012,23/12/2012 19:35,23/12/2012 21:25,AU ,AU

757,303602975,10,5,1,VA ,661,BNE ,ADL ,1/04/2013,1/04/2013 8:25,1/04/2013 11:35,AU ,AU

757,303602975,15,5,1,VA ,660,ADL ,BNE ,12/04/2013,12/04/2013 6:15,12/04/2013 9:05,AU ,AU

757,303616351,12,3,1,EK ,538,DXB ,AMD ,24/12/2012,24/12/2012 23:00,25/12/2012 3:00,AE ,IN

757,303616351,9,3,1,EK ,433,BNE ,DXB ,24/12/2012,24/12/2012 2:35,24/12/2012 13:00,AU ,AE

757,303616351,15,3,1,EK ,434,DXB ,BNE ,25/01/2013,25/01/2013 10:25,26/01/2013 6:40,AE ,AU

757,298619570,8,3,1,VA ,513,BNE ,SYD ,12/10/2012,12/10/2012 8:25,12/10/2012 11:00,AU ,AU

757,298619570,3,3,1,VA ,969,TSV ,BNE ,12/10/2012,12/10/2012 5:55,12/10/2012 7:40,AU ,AU

757,298619570,13,3,1,VA ,19,SYD ,BNE ,25/10/2012,25/10/2012 11:30,25/10/2012 12:10,AU ,AU

757,298619570,18,3,1,VA ,974,BNE ,TSV ,25/10/2012,25/10/2012 13:55,25/10/2012 15:55,AU ,AU

757,287279108,24,4,1,VA ,8,DFW ,BNE ,16/04/2012,16/04/2012 21:55,18/04/2012 5:00,US ,AU

757,287279108,56,4,1,VA ,970,BNE ,TSV ,18/04/2012,18/04/2012 8:55,18/04/2012 10:55,AU ,AU

757,287279108,84,4,1,UA ,1591,MCO ,IAH ,10/04/2012,10/04/2012 12:58,10/04/2012 14:26,US ,US

757,287279108,12,4,1,VA ,7,SYD ,DFW ,19/03/2012,19/03/2012 14:40,19/03/2012 13:50,AU ,US

757,287279108,8,4,1,VA ,521,BNE ,SYD ,19/03/2012,19/03/2012 10:25,19/03/2012 13:00,AU ,AU

757,287279108,16,4,1,VA ,4666,DFW ,MCO ,19/03/2012,19/03/2012 15:50,19/03/2012 19:10,US ,US

757,287279108,20,4,1,VA ,4507,IAH ,DFW ,16/04/2012,16/04/2012 19:50,16/04/2012 21:05,US ,US

757,287279108,4,4,1,VA ,969,TSV ,BNE ,19/03/2012,19/03/2012 5:55,19/03/2012 7:45,AU ,AU

757,251658591,9,9,1,VA ,977,TSV ,BNE ,18/06/2010,18/06/2010 19:35,18/06/2010 21:15,AU ,AU

757,251658591,18,9,1,VA ,978,BNE ,TSV ,26/06/2010,26/06/2010 19:35,26/06/2010 21:35,AU ,AU

757,256408545,10,5,1,VA ,15,BNE ,LAX ,2/10/2010,2/10/2010 11:00,2/10/2010 7:00,AU ,US

757,256408545,5,5,1,VA ,969,TSV ,BNE ,2/10/2010,2/10/2010 5:55,2/10/2010 7:35,AU ,AU

757,256408545,50,5,1,UA ,527,LAX ,MCO ,2/10/2010,2/10/2010 11:30,2/10/2010 19:23,US ,US

757,256408545,30,5,1,UA ,277,MCO ,LAX ,23/10/2010,23/10/2010 19:10,23/10/2010 21:34,US ,US

757,256408545,15,5,1,VA ,16,LAX ,BNE ,23/10/2010,23/10/2010 23:30,25/10/2010 6:20,US ,AU

757,256408545,20,5,1,VA ,970,BNE ,TSV ,25/10/2010,25/10/2010 8:55,25/10/2010 10:55,AU ,AU

757,238329255,2,2,1,BA ,31,LHR ,HKG ,13/10/2009,13/10/2009 21:15,14/10/2009 16:05,GB ,HK

757,238329255,4,2,1,VA ,98,HKG ,BNE ,14/10/2009,14/10/2009 23:15,15/10/2009 9:50,HK ,AU

757,240349038,5,5,1,VA ,974,BNE ,TSV ,15/10/2009,15/10/2009 14:00,15/10/2009 16:00,AU ,AU

Super Contributor
Posts: 490

Re: Pivoting data

It would be easier, if you give the desired output based on your above dataset.

New Contributor
Posts: 4

Re: Pivoting data

Hi Mohamed, here is a sample of what the output going to look like in csv format.

Best,

Pete

id,Travel_ID,Travel_SEGMENT_ID,Travel_PAXI,PNR_ITEM_NO,CTRY_ON_OFF_CARRIER,AIRPORT_ON_OFF,OFF_CTRY1,CTRY_DURATION1,OFF_CTRY2,CTRY_DURATION2,OFF_CTRY3,CTRY_DURATION3,OFF_CTRY4,CTRY_DURATION4,OFF_CTRY5,CTRY_DURATION5,OFF_CTRY6,CTRY_DURATION6,OFF_CTRY7,CTRY_DURATION7,CTRY_DUR,US_DUR,AU_DUR,CTRY_SPEND_MOST_TIME

747,313431699,2,2,1,US:US(VA)|US:AU(VA)|AU:AU(VA)|AU:AU(VA)|AU:AU(VA)|AU:AU(VA)|AU:US(VA)|US:US(UA),IAHSmiley Very HappyFW|DFW:BNE|BNESmiley TongueER|PER:ASP|ASP:CNS|CNSSmiley FrustratedYD|SYDSmiley Very HappyFW|DFW:IAH,US ,0,AU ,0,AU ,8,AU ,0,AU ,5,AU ,2,US ,0,US-0-AU-0-AU-8-AU-0-AU-5-AU-2-US-0,0,15,AU

777,313431699,1,1,1,US:US(VA)|US:AU(VA)|AU:AU(VA)|AU:AU(VA)|AU:AU(VA)|AU:AU(VA)|AU:US(VA)|US:US(UA),IAHSmiley Very HappyFW|DFW:BNE|BNESmiley TongueER|PER:ASP|ASP:CNS|CNSSmiley FrustratedYD|SYDSmiley Very HappyFW|DFW:IAH,US ,0,AU ,0,AU ,8,AU ,0,AU ,5,AU ,2,US ,0,US-0-AU-0-AU-8-AU-0-AU-5-AU-2-US-0,0,15,AU

787,313431699,36,4,2,US:US(VA)|US:AU(VA)|AU:AU(VA)|AU:AU(VA)|AU:AU(VA)|AU:AU(VA)|AU:US(VA)|US:US(UA),IAHSmiley Very HappyFW|DFW:BNE|BNESmiley TongueER|PER:ASP|ASP:CNS|CNSSmiley FrustratedYD|SYDSmiley Very HappyFW|DFW:IAH,US ,0,AU ,0,AU ,6,AU ,0,AU ,5,AU ,2,US ,0,US-0-AU-0-AU-6-AU-0-AU-5-AU-2-US-0,0,13,AU

Ask a Question
Discussion stats
  • 2 replies
  • 212 views
  • 0 likes
  • 2 in conversation