Look-up within same data and multiple variables with SAS hash

Question

I have a dataset as can be generated with Below code.

data have;
infile DATALINES DLM='|' DSD MISSOVER ;
input (VarA VarB) ($) ord;       
datalines;
YY|PP|3             
XX|YY|2            
|XX|1             
|BB|1             
BB|AA|2             
;
run;

I am looking for a way to look up values of VarA in VarB in such a way so that we can create grouping of those records and then do some conditional processing to get a dataset generated with the code below.

data want;
infile DATALINES DLM='|' DSD MISSOVER ;
input (VarC VarD) ($);       
datalines;
XX|PP                     
BB|AA             
;
run; 

have: For the first 3 records, XX in VarB has a corresponding record in VarA having VarB as 'YY'. Consecutively, 'YY' in VarB has a corresponding record in VarA having 'PP' as VarB.

Want: First occurrence of the group of first 3 records, which is 'XX' as VarC and last Occurrence of group which is 'PP' as VarD.

Please post in comments if need further clarification.

Thank you!

Answer 1

If your actual data is as simple as your sample data, this is quite simple with the hash object.

data have;
infile DATALINES DLM='|' DSD MISSOVER ;
input (VarA VarB) ($) ord;       
datalines;
YY|PP|3 
XX|YY|2 
  |XX|1 
  |BB|1 
BB|AA|2 
;

data want(keep = VarC VarD);
   if _N_ = 1 then do;
      dcl hash h(dataset : 'have');
      h.definekey('VarA');
      h.definedata('VarB');
      h.definedone();
   end;

   set have;
   where VarA = '';

   VarC = VarB;

   do until (rc);
      rc = h.find(key : VarB);
   end;

   VarD = VarB;
run;