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;
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".
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;
NO. I have no time to do this. Hash Table is a big topic to talk.
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.
Hi
Your code is also working fine. Thank you so much.Its easy to understand me.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.