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 ... View more

Hello, I must upload data to a SQL server and created the table which contains the column definition and which is to be updated like this: Libname stage ODBC Schema=ODBC DSN=sqlstage; Proc SQL;   Create Table stage.Blend_xx  (Scenario Char (4), Date Date, Alternative Integer, etc.); Quit; Am I right that it is no use to define Alternative's length in SAS and that I simply leave the integer-conversion to the access-interface? Thanks&kind regards ... View more