Woow ! Thanks a million Tom. It worked exactly. ... View more

Sorry but this way it does not work. Inside having condition, the value of  min(b.factor1-a.percentile) cannot be computed corrected as every time during the substraction and with min () condition the factor_1 will be substracted with the highest value ie. of P_100 (value 507) which yeild a value -455 and thus every time P_100 percentile band will be selected.  Example -   Factor 1   P_0 P_1 P_2 P_42 P_43 P_75 P_76 P_77 P_97 P_98 P_99 P_100 152   -431 -246 -209 26 30 149 154 159 320 346 379 507 28   -431 -246 -209 26 30 149 154 159 320 346 379 507   In the above table, the correct percentile band of 152 should be P_75 (value 154) and 28 should be P_43 but using your mentioned query it gives Factor 1 _NAME_ COL1 152 P_100 507 28 P_100 507     What I need is comparing the value of factor_1 with each percentile value and find the percentile bucket in which the factor_1 value is less than or equal to.   Why I have been using array is bcoz I have been used to Object oriented language and with array its easy to compare Factor_1 value iteratively with every single percentile value and saving the index where it matches -- this is how I am doing  and as it is a part of a process chain it needs to be run within pre-defined macro   SAS code for calculating the percentile band but does not work under macro as I am unable to figure out arrays inside macro - data data_band2 ; set data; array p{*} P_0 - P_100; score_perc = 0; do i = 1 to 100; if Factor_1 > p{i} and Factor_1 <= p{i+1} then score_perc = i; end; if Factor_1 < P_1 then score_perc=1; run;     ... View more

I already have a macro and wanted to add this functionality to the exisiting one.   Comming to your question, if the factor_1 has value 23 then it will fall under P_8. The condition I am using here is -  if (P_7< Factor_1 <= P_8) then P_8 and so will apply for all values.   I am not sure how it could be done without array's as for each value in table 1 I am required to iterate over all the values of table 2. Would be great if you can help me out here ! Thanks. ... View more

Thanks Paige  for your response. In table 1, I have a column value (Factor 1) which I need to compare from percentile values from table 2 ( which contains)1 to 100 percentile values) and create a column in table 1 to store the percentile bucket (eg. added in below table 1) under which the values falls ?   Iteratively I have to perform this comparision for all the row in my column/factor 1 ?   Table 1   Factor 1 Percentile Bucket 10 P_4 15 P_5 21 P_7 -8 P_0 4 P_3 -3 P_1 33 P_10     Table 2   _NAME_ Percentile P_0 -10 P_1 -5 P_2 0 P_3 5 P_4 10 P_5 15 P_6 17 P_7 21 P_8 25 P_9 30 P_10 34 ... View more