Help with Hash Has_next & Find_next

[user24feb]

Hello,

I am trying to merge records which have multiple observations (sorry, I couldn't put this clearer). For example:

Base file  -->                                 Lookup file    -->                      Result    

A_ID1 A_ID2     A_Value               B_ID1   B_ID2 B_Value            A_ID1      A_ID2 A_Value    B_ID1 B_ID2 B_Value

A         MMM      0.4                        A          MMM       0.4               A              MMM   0.4            A         MMM 0.4

A         LLL         0.6                        A          KKK         0.6               A              LLL      0.6            A         KKK   0.6

B         MMM      0.9                                                                         B               MMM   0.9           A         MMM 0.4

B         QQQ       0.1                                                                        B               QQQ   0.1            A          KKK   0.6

This is, in a sense I merge by ID_1.

The following code uses a hash object and works almost, but it keeps the predecessor of the lookup file, if the number of observations in the lookup file is smaller than the number of observations in the base file. If you run this code, line 10 gets the B_: variables of line 9, but they should be missing.

Data A;
  Input @1 A_BoM_ID $20. @22 A_Mid $3. A_Qty;
  Length A_Upper $3.;
  A_Upper=Substr(A_BoM_ID,1,3);
  Datalines;
460_01_462_01        400 0.5
460_01_462_01        401 0.5
460_01_462_02        400 0.7
460_01_462_02        498 0.3
460_01_462_03        499 0.8
460_01_462_03        401 0.2
460_01_462_04        400 1
460_01_462_05        400 0.6
460_01_462_05        401 0.3
460_01_462_05        402 0.1
460_01_462_06        400 0.7
460_01_462_06        498 0.3
463_01_462_01        400 0.9
463_01_462_01        401 0.1
463_01_462_02        498 0.6
463_01_462_02        499 0.4
467_01               400 0.5
467_01               401 0.5
467_02               400 0.5
467_02               402 0.5
468_02               400 0.5
468_02               402 0.5
468_04               400 0.7
468_04               402 0.3
;

Data A;
  Set A;
  Retain Nr;
  By A_BoM_ID;
  If First.A_BoM_ID Then Nr=0;
  Nr+1;

%Let BoM_ID=460_01_462_02;
Data B;
  Set A (Where=(B_Bom_ID eq "&BoM_ID.") Rename=(A_Bom_ID=B_Bom_ID A_Upper=B_Upper A_Mid=B_Mid A_Qty=B_Qty));
Run;

Data C (Drop=r:) ;
  Declare Hash H(Dataset:'B',Multidata:'y');
  H.Definekey('Nr');
  H.Definedata(All:'y');
  H.Definedone();
  If 0 Then Set B;
  Do Until (Eof_A);
    Set A End=Eof_A;
rc=H.Find();
If (rc eq 0) Then Do;
      H.Has_next(result:r);
   If (r ne 0) or (rc ne 0) Then Do;
     rc=H.Find_next();
     H.Has_next(result:r);
     If r eq 0 Then Call Missing (B_Bom_ID); * this line doesn't work;
   End;
End;
Output; * I think I have to keep the output here;
  End;
Run;

My question is, how can I modify the code in such a way that the predecessors of the lookup data aren't passed on?

Thanks&kind regards

[Haikuo · Accepted solution]

If I understand your request correctly, you have made your code more complicated than it needs by involving Has_next method, besides, your call missing statement would never be executed if H.Find() fails. So it is easy to fix your code by just moving the call missing statement:

Data C (Drop=r:) ;

  Declare Hash H(Dataset:'B',Multidata:'y');

  H.Definekey('Nr');

  H.Definedata(All:'y');

  H.Definedone();

  If 0 Then Set B;

  Do Until (Eof_A);

  Set A End=Eof_A;

rc=H.Find();

If (rc eq 0) Then Do;

  H.Has_next(result:r);

  If (r ne 0) or (rc ne 0) Then Do;

  rc=H.Find_next();

  H.Has_next(result:r);

  If r eq 0 Then Call Missing (B_Bom_ID); * this line doesn't work;

  End;

End;

else call missing(of b_:);

Output; * I think I have to keep the output here;

  End;

Run;

Or not using Has_next at all:

Data D;

  Declare Hash H(Dataset:'B',Multidata:'y');

  H.Definekey('Nr');

  H.Definedata(All:'y');

  H.Definedone();

  If 0 Then Set B;

  Do Until (Eof_A);

  Set A End=Eof_A;

rc=H.Find();

if rc ne 0 then do;

Call Missing ( of B_:);

output;

end;

else do rc=0 by 0 while (rc eq 0);

  output;

  rc=H.Find_next();

End;

end;

Run;