If this is a true representation of the variable names you are using you could use: %LET VARLIST=%SYSFUNC(TRANWRD(%STR(VAR1, VAR2, VAR3),VAR,A.VAR)); %PUT *************************** &VARLIST.; ... View more

Could you not query the SASHELP.VFORMAT? DATA TEST; SET SASHELP.VFORMAT; RUN; ... View more

This assumes that he is running on a Windows server. I don't believe that you can run VBS on a UNIX box (although I could be wrong). ... View more

Hi Jaap, The HASH approach was outstanding and gets the results that I want in a majority of occasions and I have made a tweak or 2 where appropriate. I have one problem in the original example I had a LO_AMOUNT which was equal to 0 when in reality the lowest LO_AMOUNT is actually 1 (I hadn't been able to see the actual data until now and had to make the assumption that the lowest value would be 0).  If I come across an AMOUNT of 0 then the result displayed is  Matrixtable=-1  ;lo_date='01jan1800'd; Lo_Freq=0; lo_amount=-1 as expected.  The problem then appears to stop the look up for subsequent observations. I have made several changes to the code to counter this including reading the hash from the beginning again and creating an if condition which creates an INT_RATE of 0 without processing the DO WHILE loop, but I am unable to get the desired outcome. My code appears as follows: DATA MATRIX; FORMAT LO_DATE DATE9.; INFILE DATALINES; INPUT LO_AMOUNT; DO MATRIXTABLE = 81,85,100; DO LO_DATE = "01JAN1980"D TO "05SEP2014"D;   DO LO_FREQ = 1,12,40,60;    INTRATE = RANUNI(0);    OUTPUT;   END; END; END; DATALINES; 1 1000 5000 10000 100000 1000000 ; RUN; PROC SORT DATA=MATRIX; BY LO_DATE; RUN; /*Outputting only every second date grouping to synthesize missing LO_DATES */ DATA MATRIX; SET MATRIX; BY LO_DATE; IF FIRST.LO_DATE THEN GROUP +1; IF FLOOR(GROUP/2) = GROUP/2 THEN OUTPUT;; RUN; DATA HISTORYTRANS; ARRAY VARS $20 VAR1-VAR40; FORMAT EFFECTIVEDATE DATE9.; DO TABLE = 81,85,100; DO EFFECTIVEDATE = "01JAN1980"D TO "05SEP2014"D BY CEIL(RANUNI(0)*100);   DO FREQ = 1 TO 60 BY CEIL(RANUNI(0)*60);    DO AMOUNT = 0 TO 1000000 BY CEIL(RANUNI(0)*1000000);     DO I = 1 TO 40;      VARS{I} = "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA";     END;     IF RANUNI(0) < .20 THEN OUTPUT;    END;   END; END; END; RUN; proc sort data=matrix; by descending matrixtable descending lo_Date descending lo_freq descending lo_amount; run; proc sort data=HISTORYTRANS  ;by descending table descending EffectiveDate descending freq descending amount  ; run; DATA LOOKUP2; retain RC ; SET HISTORYTRANS (WHERE = (EFFECTIVEDATE > "01JAN1980"D)); IF _N_ = 1 THEN DO;   DECLARE HASH H(DATASET:"MATRIX",ORDERED:"D", HASHEXP:16);   DECLARE HITER HI("H");   H.DEFINEKEY("MATRIXTABLE","LO_DATE","LO_FREQ","LO_AMOUNT");   H.DEFINEDATA("MATRIXTABLE","LO_DATE","LO_FREQ","LO_AMOUNT","INTRATE");   H.DEFINEDONE();   Call missing(MATRIXTABLE,LO_DATE,LO_FREQ,LO_AMOUNT,INTRATE) ;   /* add addtional stops for up/down without error getting there  */   Matrixtable=-1  ;lo_date='01jan1800'd; Lo_Freq=0; lo_amount=-1; RC = H.ADD();   Matrixtable=9999;lo_date='01jan2200'd; Lo_Freq=0; lo_amount=-1; RC = H.ADD(); END; format LO_DATE date9. ;    DO WHILE (RC = 0 AND INT_RATE = .);    IF MATRIXTABLE = TABLE   THEN DO;      Do While (LO_DATE > EFFECTIVEDATE ) ;        RC = HI.NEXT();      end ;      Do While (LO_FREQ > FREQ ) ;        RC = HI.NEXT();      end ;      Do While (LO_AMOUNT > AMOUNT) ;        RC = HI.NEXT();      end ;      INT_RATE = INTRATE;    END;     Else Do;     RC = HI.NEXT();   END;   END; OUTPUT ; DO WHILE (LO_DATE <= EFFECTIVEDATE & RC = 0 );    RC = HI.PREV();  /* get back to starting point of date - previous date */ END; run; Thank you again for all your help. ... View more

Thank you all for your help and insights. In my initial question I simplified the issue considerably in an effort to engage more people.  In doing so I may have over simplified and caused greater confusion. The bigger story is as follows: Basically I have 4 variables by which I need to merge.  1 variable (table) must equal the value in the in the matrix table and the other 3 should be greater than or equal to the value in the matrix.  The below produces a simulated version of the datasets I am working with. DATA MATRIX; FORMAT LO_DATE DATE9.; INFILE DATALINES; INPUT LO_AMOUNT; DO MATRIXTABLE = 81,85,100; DO LO_DATE = "01JAN1980"D TO "05SEP2014"D;   DO LO_FREQ = 1,12,40,60;    INTRATE = RANUNI(0);    OUTPUT;   END; END; END; DATALINES; 0 1000 5000 ; RUN; DATA HISTORYTRANS; ARRAY VARS $20 VAR1-VAR40; FORMAT EFFECTIVEDATE DATE9.; DO TABLE = 81,85,100; DO EFFECTIVEDATE = "01JAN1980"D TO "05SEP2014"D BY CEIL(RANUNI(0)*100);   DO FREQ = 1 TO 60 BY CEIL(RANUNI(0)*60);    DO AMOUNT = 100 TO 1000000 BY CEIL(RANUNI(0)*1000000);     DO I = 1 TO 40;      VARS{I} = "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA";     END;     IF RANUNI(0) < .20 THEN OUTPUT;    END;   END; END; END; RUN; In the methods suggested thus far I am concerned about the number of times I will have to access each table to either group and sort, or create mountains of formats.  As a result I ran with a HASH ITERATOR. DATA LOOKUP; IF 0 THEN SET MATRIX; IF _N_ = 1 THEN DO; DECLARE HASH H(DATASET:"MATRIX",ORDERED:"D", HASHEXP:16); DECLARE HITER HI("H"); H.DEFINEKEY("MATRIXTABLE","LO_DATE","LO_FREQ","LO_AMOUNT"); H.DEFINEDATA("MATRIXTABLE","LO_DATE","LO_FREQ","LO_AMOUNT","INTRATE"); H.DEFINEDONE(); END; SET HISTORYTRANS; RC = HI.FIRST(); DO WHILE (RC = 0 AND INT_RATE = .);      IF MATRIXTABLE = TABLE THEN                IF LO_DATE <= EFFECTIVEDATE THEN                 IF LO_FREQ <= FREQ THEN                      IF LO_AMOUNT <= AMOUNT THEN DO;        INT_RATE = INTRATE;        LEAVE;       END;      RC = HI.NEXT(); END; RUN; I have seen an improvement in my processing time, down from 1.5Hrs down to 1Hr (ensuring that the 2 datasets remained static at 456012 records - Matrix and 30000 records - HistoryTrans ), but I would like to improve this further.  Will the previous recommendations (@JAAP and @KSHARP ) work under the above conditions? Could I please seek your further assistance? Regards, Scott ... View more

Hi Jaap. Unfortunately I am constrained using 9.1.3, hence no multi data option. Is there a work around for this? ... View more