DATA Step, Macro, Functions and more

First occurrence logic please

Reply
Frequent Contributor
Posts: 86

First occurrence logic please

Hi,

I have a data set in that three variables like 

Boarding point      Destination point      Distance_KM

Delhi                    Mumbai                    2000

Mumbai               Delhi                         2000

Delhi                    Kolkata                    4000

Kolkata                    Delhi                    4000

Mumbai               Kolkata                    6000

Kolkata               Mumbai                    6000

.........                 .........                     .........

.........                 .........                     .........

.........                 .........                     .........

.........                 .........                     .........

How to find a 'First occurrence' ?

How to do 'First. & Last.'

can any one please help me Smiley Sad

Thanks,

Suresh

PROC Star
Posts: 7,470

Re: First occurrence logic please

In your example, which records are you trying to identify and based on which factors?

Frequent Contributor
Posts: 86

Re: First occurrence logic please

Hi Arthur,

Boarding point      or     Destination point

Thanks,

Suresh

Super User
Posts: 11,343

Re: First occurrence logic please

I'm guessing that you're looking to get one of each boarding destination pair. So something like this maybe:

data intermediate;

     length link $ 200.; /* long enough to take both boarding and destination together*/

     if upcase(boarding) ge upcase(destination) then link = catx(' ',boarding, destination);

     else link = catx(' ', destination,boarding);

run;

proc sort data=intermediate; by link; run;

data want;

     set intermediate;

     by link;

     if first.link;

run;

PROC Star
Posts: 7,470

Re: First occurrence logic please

I'm also just guessing what you want.  If you want to know which record a city was identified as being either a boarding or destination point, you could do it using the in function with arrays.  E.g.:

data have;

  input Boarding_point $ Destination_point $ Distance_KM;

  cards;

Delhi                Mumbai                    2000

Mumbai               Delhi                     2000

Delhi                Kolkata                   4000

Kolkata              Delhi                     4000

Mumbai               Kolkata                   6000

Kolkata              Mumbai                    6000

;

data want (keep=City Record_Number Type);

  array cities(100) $;

  retain cities;

  set have;

  if not(Boarding_point in cities) then do;

    j+1;

    cities(j)=Boarding_point;

    Record_Number=_n_;

    City=Boarding_point;

    Type="BP";

    output;

  end;

  if not(Destination_point in cities) then do;

    j+1;

    cities(j)=Destination_point;

    Record_Number=_n_;

    City=Destination_point;

    Type="DP";

    output;

  end;

run;

proc print data=want;

run;

output

Obs  Record_Number  City     Type

1        1       Delhi       BP

2        1       Mumbai      DP

3        3       Kolkata     DP

Super Contributor
Posts: 349

Re: First occurrence logic please

Hi,

Try this...is this what you want(Guess)

data have1;

  input Boarding_point $ Destination_point $ Distance_KM;

  newvar=catx('',Boarding_point,Destination_point);

  newvar1=catx('',Destination_point,Boarding_point);

  xx=lag(newvar1);

  if xx=newvar then output;

  cards;

Delhi                Mumbai                    2000

Mumbai               Delhi                     2000

Delhi                Kolkata                   4000

Kolkata              Delhi                     4000

Mumbai               Kolkata                   6000

Kolkata              Mumbai                    6000

;

run;

Ask a Question
Discussion stats
  • 5 replies
  • 237 views
  • 6 likes
  • 4 in conversation