Find last child

[chang_y_chung_hotmail_com]

If the OP wants a truely dynamic solution, here is one wrapping Astonding's excellent solution in a  function, so that the latest descendant is found on-the-fly.

  data one;
    input date :date11. (parent child) (:$3.);
  cards;
  01may2012 001 022
  01may2012 032 019
  02may2012 019 032
  02may2012 022 005
  03may2012 005 022
  03may2012 032 017
  04may2012 017 016
  ;
  run;

  /* for the given ancestor, return the latest descendant.
    borrowed from Astounding in the thread 35133 "Find last child."
    assumes that data are chronologically ordered. */
  %macro LD;
    %global ancestor LD;
    %let ancestor = %sysfunc(dequote(&ancestor));
    data _LD;
      do until (start = "&ancestor");
        set one(rename=(parent=start child=label)) nobs=nobs;
        i + 1;
      end;
      do j = i + 1 to nobs;
        set one point=j;
        if parent = label then label = child;
      end;
      output;
    run;
    data _LD;
      set _LD end=end;
      if end then call symputx("LD", label, "global");
    run;
  %mend LD;

  proc fcmp outlib=work.func.LD;
    function LD(ancestor $) $3;
      length LD $3;
      rc = run_macro("LD", ancestor, LD);
      return(LD);
    endsub;
  quit;

  %let cmplib = %sysfunc(getoption(cmplib));
  options cmplib = (work.func &cmplib);

    /* check */
    data _null_;
      do ancestor = "001", "032", "019", "022", "005", "017";
        latest_descendant = LD(ancestor);
        put ancestor "--> " latest_descendant;
      end;
    run;
    /* on log
    001 --> 022
    032 --> 016
    019 --> 016
    022 --> 022
    005 --> 022
    017 --> 016
    */

  options cmplib = (&cmplib);

[Ksharp]

Hi. Patrick.

I might understand what you mean.

You want to chain them up by time series order, and the obs are stored by time series order.

But there is a scenario I want know, if there are multi id_from at the following obs , what you are going to do ?

Sample data:
01may2012 001 022
01may2012 032 019
02may2012 019 032
02may2012 022 005
03may2012 005 022
03may2012 032 017
04may2012 032 016

I only take the first appeared obs.

data have;
  input date:date9. id_from $ id_to $;
  format date date9.;
  datalines;
01may2012 001 022
01may2012 032 019
02may2012 019 032
02may2012 022 005
03may2012 005 022
03may2012 032 017
04may2012 017 016
;
run;

data want(keep=start label);
 if _n_ eq 1 then do;
  length id_from $ 8;*if 0 then set have;
  declare hash ha(ordered:'y');
   ha.definekey('id_from');
   ha.definedone();
end;

set have;
  declare hash ha1(ordered:'y');
  declare hiter hi1('ha1');
   ha1.definekey('id_from');
   ha1.definedata('id_from');
   ha1.definedone();

if ha.check() ne 0 then do;
     ha.replace();ha1.replace();id_from=id_to;ha.replace();ha1.replace();
     do i=_n_+1 to nobs ;
      set have(keep=id_from id_to rename=(id_from=_id_from id_to=_id_to)) point=i nobs=nobs;
      if id_to eq _id_from then do;id_from=_id_to;ha.replace();ha1.replace();id_to=_id_to;end;
     end;
 label=id_to;
 do while(hi1.next()=0);
  if id_from ne id_to then do;start=id_from;output;end;
 end;
end;
run;

Ksharp

[Patrick]

Hi all

Thanks a lot for all your good solutions and sorry for replying only now.

I've tested them all - throwing some more data at it - and everything after Astounding's code with the little fix from Linlin worked.

As said in the original post the volumes I'm dealing with are low (couple of thousand rows) and simplicity of code is more important than performance.

That's why I'm going with Astounding's solution. And in case there should be performance issues then Ksharp's second take on the problem will be the remedy.

To answer your question Ksharp "But there is a scenario I want know, if there are multi id_from at the following obs , what you are going to do ?".

That can't happen. Per chain there will be maximum of one change per day, and an ID can't be member of more than one chain.

chang_y_chung

I think I understand what you had in mind but it takes things further than I need it for my real life situation. Thanks anyway for the idea.

Art

Thanks for the link. I had a look at it and sure could have taken some inspiration from there. Nicely for me people gave me here in this forum already a fully working and simple solution - exactly what I needed.

(for some reason it doesn't let me mark your answer as "helpful" - sorry about that).

Thanks again to everybody!

Patrick

[Alpay]

Hi Patrick,

You may also try the following data steps using hash object.

Zafer

data have;

  input date:date9. id_from $ id_to $;

  format date date9.;

  datalines;

01may2012 001 022

01may2012 032 019

02may2012 019 032

02may2012 022 005

03may2012 005 022

03may2012 032 017

04may2012 017 016

;

run;

data _null_;

  length l $4000 id $3;

  if _n_ = 1 then do;

    declare hash h();

    h.defineKey('id');

    h.defineData('id','l');

    h.defineDone();

  end;

  set have end=last;

  id = id_from;

  if h.find() ne 0 then do;

    id = id_to;

    l = CATX('/',id_from,id_to);

  end;

  else do;

    l = CATX('/',l,id_to);

    h.remove();

    id = id_to;

  end;

  h.replace();

  if last then h.output(dataset:'h');

run;

data idfmt;

  if _n_ = 1 then do;

    declare hash h();

    h.defineKey('Start');

    h.defineDone();

  end;

  set h;

  length Start Label $3;

  retain fmtname '$idfmt';

  Label = id;

  i=1;

  Start = SCAN(l,i,'/');

  do while(Start ne '');

    if Start ne Label and h.find() ne 0 then do;

      h.replace();

      put (Start Label)(=);

      output;

    end;

    i+1;

    Start = SCAN(l,i,'/');

  end;

  keep Start Label fmtname;

run;

[raulbq_telefonica_net]

Hellow thank you,  accept me  in your network I just have read it your opinion I am agree with you with your solutions, but I have not sample enougth. That´s it is all.

Please can you help me because I have a problem about configure system computer , monitor, excel SQL, therefore I am working all the time use it , proc glm etc.

My regards I understood other meaning.  bye