SAS Support Communities

Hello All,   Problem:   I have a Data Cleaning problem. I have this data where There is a Product manufacturer and Product Brand. Each manufacturer has several Brands. The brands data is manually entered and is messy. I need to check the data which is manually entered and create standard brand name.   Here is the example,   Product Manufacturer Product Brand Abila Taquila Abila TAQUILA Abila tAqueela Tambola Daqueela Tambola Taqueela Abila daqueela Tambola Samila   Here is the desired output, Product Manufacturer Product Brand Standard Brand Abila Taquila Taquila Abila TAQUILA Taquila Abila tAqueela Taquila Tambola Daqueela Daqueela Tambola Taqueela Taquila Abila daqueela Daqueela Tambola SAMmila Samila   The next step would be get the count of unique combinations. Which I can achieve once I got the above output. The problem here is, I need to do this for only 2 Manufacturers but all brands there are (~3,000) of them.   Approach:   I did run a select distinct brand name and got around 200,000 names which are different combinations of same name. For example, the result retuned as Abila abila abla cabla Cabl Cabla .. So, Since I cannot extract all possible combinations, I extracted count as well, which gave me most frequent used notation, I ran a program to delete all less frequent notations of brand name. Now I have list of Product Brands ~90,000   But, I cannot code multiple WHEN … THEN Statements like below (I should code (90,000) of them, which is impossible)   CASE      WHEN MISC.PRD_BRAND_NM Like 'ABila%' OR MISC.PRD_BRAND_NM Like  'abila%' OR MISC.PRD_BRAND_NM Like  'ab%' THEN 'Abila' AS Standard_name I’m looking is there a way that I create a macro and pass all variables through a function to create standard name?   Some thing like below,   CASE      WHEN MISC.PRD_BRAND_NM Like &pass. OR MISC.PRD_BRAND_NM Like  &pass. OR MISC.PRD_BRAND_NM Like  &pass. THEN &pass. AS Standard_name Do <this function> until <end of all brands> <this function> To replace macro variables with all combinations.   Or any other way I can achieve the objective? Thanks a ton! ... View more

Hello all,   Below is the code.     options mprint symbolgen; data have; input EMPNO Salary empcode $; cards; 111 4000 A 112 6000 A 114 2000 A 115 8000 A 223 2000 B 226 1000 B 228 3000 B 300 500 C 333 700 C 345 300 C 356 200 C 320 700 C ; run; %macro highest(num=); %global g_num; %let g_num = &num; proc sort data=have; by descending salary; run; data want; set have; if _n_ = &g_num then do; output; stop; end; run; proc print data = want; %if &g_num = 1 %then %do; title "This is 1st Highest Salary"; %end; %else %if &g_num = 2 %then %do; tilte "This is 2nd Highest Salary"; %end; %else %if &g_num = 3 %then %do; title "This is 3rd Highest Salary"; %end; %else %do; title "This is &g_num.th Highest Salary"; %end; %symdel g_num; %mend highest; %highest(num=11)     I want to print nth highest salary.   At first my program executes fine. And If you execute changing the parameter value(num) in macro %highest, along with macro it yields correct results.   But if I execute only the line  %highest(num=11) by changing the value, without including the macro definition in the execution, it shows results of previous value.    What's wrong with this code ?    Thanks, Vaibhav   ... View more