SAS Scenario

[neha_gupta]

Hi 

Could you please help me for the same? I do have data like below sample data.

Data Flight;

input id source $ destination$;

datalines;

1 Delhi  Bangalore

2 Delhi  Goa

1 Bangalore  Goa

2 Goa Bangalore

1 Goa Delhi

;

Questions

===========>

1. Output data set Should be RoundTrip

id Roundtrip

1 Yes

2 No

2 What is last destination of  person which is not completed Round trip.( For example id 2 is not completed the round trip and his last destination is  Bangalore)

[ballardw]

What is the definition of a "round trip" in terms of the data content? Are you looking for a data set as a result or a report for people to read? You also mention "person" but it is not obvious from your data that ID is a person.

What have you tried so far? What result were you not able to get?

Since this looks like course homework you'll get a lot more from any answer if you have at least attempted something. You may have been close but missed a thing or two and we'll gladly help with that.

One might also say that without dates it is hard to determine if something is truly a round trip as we don't know in what order any of the flights may have occurred and if there are 3 years between flights it would not normally be considered a "round trip".

[Ksharp · Accepted solution]

If there was only one to one match, that would be easy.

Data Flight;
input id source $ destination$;
datalines;
1 Delhi  Bangalore
2 Delhi  Goa
1 Bangalore  Goa
2 Goa Bangalore
1 Goa Delhi
;
data want;
 if _n_=1 then do;
    if 0 then set flight;
    declare hash h(dataset:'flight');
    h.definekey('id','source');
    h.definedata('destination');
    h.definedone();
 end;
set flight;
temp=source;
do while(h.find(key:id,key:temp)=0);
 temp=destination;
 if source=temp then leave;
end;
 Roundtrip=ifn(source=destination,1,0);
drop temp;
run;
 

[neha_gupta]

Yes,Its working fine. Could you please explain me?

[Ksharp]

NO. I have no time to do this. Hash Table is a big topic to talk.

[Kurt_Bremser]

Sort and data step.

Data Flight;
input id source :$20. destination :$20.;
datalines;
1 Delhi  Bangalore
2 Delhi  Goa
1 Bangalore  Goa
2 Goa Bangalore
1 Goa Delhi
;
run;

proc sort data=flight;
by id;
run;

data want;
set flight;
by id;
retain depart;
length depart $20;
if first.id then depart = source;
if last.id
then do;
  if depart = destination then last_destination = 'Roundtrip';
  else last_destination = destination;
  output;
end;
keep id Last_destination;
run;

Note I added formats in the first step, so that the default length of 8 is not used.

I assume your real dataset will contain dates and times, so you should use those in the sort to make sure a correct order is achieved.

[neha_gupta]

Hi 

Your code is also working fine. Thank you so much.Its easy to understand me.