BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
neha_gupta
Calcite | Level 5
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)
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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;
 

View solution in original post

6 REPLIES 6
ballardw
Super User

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
Super User

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
Calcite | Level 5
Yes,Its working fine. Could you please explain me?
Ksharp
Super User

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

Kurt_Bremser
Super User

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
Calcite | Level 5

Hi 

 

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1699 views
  • 0 likes
  • 4 in conversation