SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Help with Hash Has_next & Find_next

Accepted Solution Solved
Reply
Super Contributor
Posts: 340
Accepted Solution

Help with Hash Has_next & Find_next

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=rSmiley Happy ;
  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


Accepted Solutions
Solution
‎01-14-2015 04:44 PM
Respected Advisor
Posts: 3,156

Re: Help with Hash Has_next & Find_next

Posted in reply to user24feb

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=rSmiley Happy ;

  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_Smiley Happy;

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_Smiley Happy;

output;

end;

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

  output;

  rc=H.Find_next();

End;

end;

Run;

View solution in original post


All Replies
Solution
‎01-14-2015 04:44 PM
Respected Advisor
Posts: 3,156

Re: Help with Hash Has_next & Find_next

Posted in reply to user24feb

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=rSmiley Happy ;

  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_Smiley Happy;

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_Smiley Happy;

output;

end;

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

  output;

  rc=H.Find_next();

End;

end;

Run;

🔒 This topic is solved and locked.

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

Discussion stats
  • 1 reply
  • 421 views
  • 0 likes
  • 2 in conversation