Hi Matt, I hope you had a good weekend. Sorry, for the late acknowledgement as I couldn't implement your solution until today at office and as I did today I must confess you are the messiah of SAS. Although, with hope I was eagerly anticipating your response, you seem to marvel me with your awesome "thought and application". Well, I guess Stars are born rather than made. What a code!!Great Performance. Now, the problem is there aint many Matt's in our office so that's a bit of challenge to even maintain such sophisticated code, but I am so inspired and have welcomed the challenge to learn more. Many Many thanks. Have a great day, Charlotte from Liverpool, England ... View more

Hi, It would be courteous if you could mark the question as answered and gift the appropriate points to the respondents for correct and useful answers. I do ask questions and I always make sure to recognise the help. It's only fair! ... View more

A True Class act!, Brilliant. Late to the party, Well, but first to open the champagne bottle. Yes , thanks to our healthy budget we have great RAM capacity. Thanks everyone, 1.genius and good friend- Xia 2. RW9  3. Robert Virgile-Astounding "I have bought a  copy of your macro magic book" 4. John-Data _null_. Thanks for making my evening a pleasant one indeed!!!. Good night from Liverpool England, Charlotte ... View more

Well Sir, Then it goes back to square as your result will also join as DN's and mine: a 1 f 2 k 3 p 4 b 1 g 2 l 3 q 4 which unfortunately doesn't meet the wanted output. ... View more

Hi John and Robert, Thank you for the responses. I ran both of your codes, however I am afraid neither of the results are appending the variables as wanted one below the other, rather it's appending the values of variables by the observation. If you agree with me, your results are the same as what the below code produces data vars;    length var1-var4 $1;    input var1-var4;    cards; a f k p b g l q c h m r d i n s e j o t ;;;; data want; set vars; do i=var1,var2,var3,var4; index=_n_; output; end; keep i index; run; : Please accept my apologies if I didn't explain well enough while you guys are offering great help. Basically, I need to append columns one below the other as is i.e even the last sort step in RW9's code will not meet the requirement because the values need not be a,b, c, d based sequence or any other predictable sequence. In other words, its just an append, however the challenge  is that there are too many variables. Hence the scalability of the code also matters big time. Sincerely, Charlotte ... View more

Good evening Sir, I hope you had a good weekend while it's been freezing cold in Liverpool, England.Thank you so much yet again for your amazingly elite style approach to the problem. Wish, you were in England :smileyblush: I tested your code and the comments associated with it are very comfortable to follow and comprehend. It successfully picks the middle rating for 4th criteria however for other scenarios it is picking the higher rating or rank rather than the lower rating. For example, here is the output: Retail 900135 Baa1 A- 4 50% Wholesale 900135 Baa1 A- 4 40% SME 900135 Baa1 A- 4 60% So, for ID 900135 the R_pct for the above on picking the lower rating should be 100%,90% and 110% for retail, Wholesale and SME respectively. Other than that change, It's simply very elegant. Trust it's not as cold in PA, Charlotte ... View more

I agree, I love this interaction anyway, and that only inspires me more and more, and as a matter of fact I am starting to devote my weekends(sat and Sun) to learn SAS more thoroughly, so no pub(giving up my englishness lol), no fun and no boy friend  hahaha but true. Nevertheless, at present it seems like one needs to first be a Matt or Xia or Jaap to even understand their answers so i need to become one to not irritate either of you guys. Wish I could express my heartfelt thanks to Matt, as my intention was never to take advantage. I'm not smart but I promise I am nice, Charlotte ... View more

Thank you Sir, I did the change in the last piece as suggested and ran the code.  However I noticed that there are quite a few missing values for r_pct as it hasn't picked for some of the id's. Honestly, the reason I am finding the 4th criteria so hard to understand is because comprehending the combination it presents confuses me. For example- if dataset one has: ID                Rating1        Rating2   Rating3 10540            A                  B           C and in Dataset2(the look up table) if A, B,C are all found in the same row then obviously I recognise there can be only one R% to fetch. If A and B are in one row and C happens to be in another row then I would have to pick R% the lower of the two ratings because in this case A and B will have the same R%. And most importantly, if A, B and C i.e each one of these are found in 3 different rows, this is where I need to pick the R5 using the middle rating and not the lowest. Understanding this makes me mental:smileycry: . Hence the reason, I reached out for help. /*changed code */ data x; set one; array a[3] rating1-rating3; array fmt[3] $ 6 _temporary_ ('qrank.' 'rrank.' 'srank.'); array b[3] _temporary_; do _n_=1 to 3; if missing(a[_n_]) then continue; b[_n_]=inputn(a[_n_], fmt[_n_]); end; call sortn(of b ); *r_pct=put(min(of b , 24), rank2pct.); /* removed this*/ r_pct = put(ifn(nmiss(of b )=0, b[2], b[1]), rank2pct.); run; /*Below is the Output where I noticed missing r_pct for id's that have one or more ratings. id rating1 rating2 rating3 r_pct 900129 2000425 900135 Baa1 A- 2000328 900152 Baa1 NR A 900265 Ba1 BBB- 100% 900005 Baa1 A+ 20% 900108 Baa2 BBB+ A 100% 900121 A1 A A+ 50% 900008 A2 A+ 50% 6964444 Baa3 BB+ 100% 900149 BBB+ 900124 000100-5 900153 A2 A+ 50% 2000315 50% 900136 Baa1 BBB+ 100% 900122 Aa3 AA- AA- 20% 900155 A3 BBB+ BBB+ 100% 1103127 100% 900185 A1 A+ 50% 900151 Baa1 50% 900172 A2 A- A 20% 900007 A- A ... View more

Good morning,Thank you and really sorry, I was a bit too overambitious and really fancied your first approach before reality bit me hard. :smileycry:To keep it simple, what I did was I filtered the dataset1 to output three different datasets  based on the market_type condition, thus having 3 subsets, one for retail, one for wholesale and one for SME. This makes it easier for me to get back to using the original formats method code you helped me with and I can run three times. No problems there as I don't have to worry about the additional criteria 1,2 and 3. But this condition dumps me though- 4. The logic to get the R% remains same, expect "when there are 3 ratings for an ID, I need to pick R% for ID's using the  middle of the 3 lkup_ratings instead of the lowest as you did before otherwise for remaining occurances such as 1 or 2 ratings the same lowest lkup ratings applies as before." Middle rating??? makes it very difficult for me whereas min or max is rather clear and easy to understand though In the last part of your format code: do _n_=1 to 3; if missing(a[_n_]) then continue; b[_n_]=inputn(a[_n_], fmt[_n_]); end; r_pct=put(min(of b , 24), rank2pct.); /*logic change or addition here?to add the condition to pick the middle rating R% when there are 3 ratings?*/ Thanks so much, Charlotte ... View more

Sir, Good evening from England, Sorry for the bother yet again, May i request your help in modifying your code that brilliantly uses the multi dim temp array/*my comment- that was a class act of yours*/ or even the formats approach to work for a slightly altered requirement:- 1. The Dataset1 has another variable by the name Market_type, that has 3 kinds of values namely "Retail", "Wholesale" and "SME". What it means is that the ID, being a business entity belongs to one of the 3 market types. 2. Dataset 2, being the rating look up Data set remains the same, however there is one separate "Rating look up table" for Retail, and one for wholesale and likewise one for SME. So In essence there are 3 different rating look up data sets. 3. Now the point is that, the look up needs to be done from Dataset 1 <---> Retail look up dataset for id's that are retail in market_type exclusively. And similarly from Dataset1 <---> Wholesale look up table for id's that are wholesale and Dataset1<--->SME look up table. /*Sounds like a filter to be applied in dataset1 for my novice level of understanding*/ 4. The logic to get the R% remains same, expect "when there are 3 ratings for an ID, I need to pick R% for ID's using the  middle of the 3 lkup_ratings instead of the lowest as you did before otherwise for remaining occurances such as 1 or 2 ratings the same lowest lkup ratings applies as before." 5. If there are no ratings for an ID with blank values, that means the ID falls into the category of Unrated, which will have to pick the default R% from respective Ratings datasets from the unrated observation. /*The weird thing here is that in some rating look up table, the unrated observation is represented as blank unlike what you see in the last row in Dataset2 where it is clearly mentioned*/ 1 UR Unrated Unrated Unrated I fear whether I have explained well enough to make it easy enough to understand. If not, please let me know if I can explain better. I'd sincerely appreciate your help. Many thanks as always! With Pleasant Regards, Charlotte ... View more

Beautiful and neat, Thank you Sir, In your second example wouldn't the contents of the PDV include variable descriptors of both datasets with missing values to variables of dataset1? If yes, dropping or keeping them be necessary later? /*coz i thought if 0 then set would still fully compile the entire descriptive portions occupying more memory but doing it one pass is awesome too, may i ask what do you reckon would be most efficient?*/ Please accept my apologies if i am wrong and silly. @hkuo @datsp and RW, Many thanks for the quick response. I really appreciate it Regards, Charlotte from England! ... View more