DATA Step, Macro, Functions and more

SAS Scenario

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

SAS Scenario

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)

Accepted Solutions
Solution
‎03-19-2018 10:50 AM
Super User
Posts: 10,686

Re: SAS Scenario

Posted in reply to neha_gupta

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


All Replies
Super User
Posts: 13,304

Re: SAS Scenario

Posted in reply to neha_gupta

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".

Solution
‎03-19-2018 10:50 AM
Super User
Posts: 10,686

Re: SAS Scenario

Posted in reply to neha_gupta

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;
 
Occasional Contributor
Posts: 6

Re: SAS Scenario

Yes,Its working fine. Could you please explain me?
Super User
Posts: 10,686

Re: SAS Scenario

Posted in reply to neha_gupta

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

Super User
Posts: 9,883

Re: SAS Scenario

Posted in reply to neha_gupta

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 6

Re: SAS Scenario

Posted in reply to KurtBremser

Hi 

 

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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