good day,   I am do some checkings on my data base.                Name row1 row2   if row1_name =row2_name then checking="Y"   and the result is "Y"   how can i avoid this?   thanks in advance    Regards, Harry   ... View more

good day,   i am using function index for searching the keyword from my data but some error happen and here is the example   NAME Liverpool Liverpoollowolo club Liverpool   data want; data have; if index(NAME,"Liverpool") then output; run;   The final data will contain these three names but the second (Liverpoollowolo) is not the result i want. how can i fix this?   can i add space to the keyword like below? if index(NAME," Liverpool ") then output; but i am afraid that the first one will fail cause there is no space before the name Liverpool   what i am looking for is a separate "Liverpool" in my new data set    Regards, Harry   ... View more

good day,   here is the problem i can't solve i have assigned a manual name to my data set as name now i want to do some sample check, can i randomly draw 5 results out for each name to Verify? and i would like to place them in the same row like this   name  ||  merchant_strip1 ||  merchant_strip2 || merchant_strip3 || merchant_strip4 ||merchant_strip5     here is my raw data set   merchant_strip Name A A ALFA TAXI DI FIORI GENESTRERIOTICH TAXI A A OPTICAL HONG KONG HKG OPTICAL A A OPTICAL HONG KONG HKG OPTICAL A A OPTICAL HONG KONG HK OPTICAL A A OPTICAL YUEN LONG HK OPTICAL A A OPTICAL YUEN LONG HKG OPTICAL A AIRLINE TAXI MISSISSAUGA CAN TAXI A AIX TAXI AIX EN PRO FR * TAXI A ALEHOP AER VALENCIAMANISES ES ALEHOP A B A L TOWING PTY L THORNLEIGH AUS BURBERRY A B C DISCOUNT CARPETS CROWS NEST AU CARPET A B SUPER MARCHE GHAZIABAD IN A B SUPER MARCH SUPERMARCHE A BAGHLOUL TAXI GENEVE CH TAXI A BAKERY CENTRAL A BAKERY A BAKERY SHATIN A BAKERY A BAKERY TAIKOO A BAKERY A BAKERY SHATIN A BAKERY A BAKERY C HKG HKG A1 BAKERY A BAKERY CAFE HONGKONG HK A1 BAKERY   Regards, Harry ... View more

good day,   here is the code i wrote. the program logic is to extract the data i needed and put it in ned data set with a new assigned name.   any skill can enhance this script? can i just simply mark the new_name in old data set?   instead of draw them out and append to a new data base.   i just find out i create many data sets and waste tons of time on waiting.   %macro sqlmerchant(A=,B=,C=,D=); PROC SQL; CREATE TABLE &A AS SELECT * ,"&b" AS NAME ,"Y" as Cleaned_flag FROM TESTING WHERE NEW2 LIKE &C AND "Merchant Category"N IN &D ; DELETE FROM TESTING WHERE NEW2 LIKE &C AND "Merchant Category"N IN &D ; create table DATA_&A as select * from &A ; quit; proc append base=testing2 data=DATA_&A ; run; %MEND; %sqlmerchant(A=AIRSTANA,b=AIR STANA,C="%AIRASTANA%",D=("Airlines----AIR STANA")); %sqlmerchant(A=AIRSTANA,b=AIR STANA,C="%AIRSTANA%",D=("Airlines----AIR STANA"));   thanks for the helping, Harry ... View more

good day,     below is my program %macro sqlmerchant(A=,B=,C=,ab=);   PROC SQL; CREATE TABLE &A AS SELECT * ,"&ab" AS NAME ,"Y" as Cleaned_flag FROM Merch_clean_latest_raw_backup WHERE Merchant_Name LIKE &b AND "merchant_name_alias.mcc"N IN (SELECT MCC FROM Merchant_list_v2 WHERE 'Merchant Category'n in &C ) ; %MEND;   %sqlmerchant(A=MCDONALD,B="%MCDONALD%",C=("Miscellaneous Stores"),ab=Mcdonald); %sqlmerchant(A=WATSONSWINE,B="%WATSONSWINE%",C=("Miscellaneous Stores"),ab=Watsons Wine); %sqlmerchant(A=WATSONS,B="%WATSONS%",C=("Miscellaneous Stores"),ab=Watsons);     here is my macro code.I have to search a list of merchant in a big data base.   and the list of merchant name and merchant code are stored in two excel.   can i set up a program which can let the macro read from the excel automatically . so that i can avoid to input all my name list into macro.   thanks in advance, Harry ... View more

Good Day,   can i scan a word by other string?   string1                                                             string2   ALIPAYHKCUBER COMPANY HKG              ALIPAYHKCUBERCOMPANY the result i want is  ALIPAYHKCUBER COMPANY   and also , can i develop a program that can extract string before blank   for this example, i want to drop all the character after two blank   ALIPAYHKCUBER COMPANY HKG    thanks in advance Harry   ... View more

good day   is there any function can count the blank from name string   FACEBK AABMSW FBMEADS IRL   like there are 3 blanks over the name string       i also do some data cleansing on this record and the result is FACEBKAAB   can i using that cleansing result and  scan/crosscheck the original record and show below result?   because i did the compress on my cleaned record and i wanted to keep that blank in this attempt of crosscheck   FACEBK AAB   so that i can know how well is my cleansing.   thanks in advance Harry ... View more

good day,   is there a way to empty a column with only two character?   i want to eliminate the row only store two character   for  example   Location   AB   Church company cc bb dd   want to empty the string AB,cc,bb,dd to nothing      thanks in advance harry ... View more

good day all,   can i separate a name by blank?   for example    Name Liverpool  Virgil van Dijk   separate a name by blank into 4 columns   column1    column2  column3  column4 Liverpool    Virgil       van          Dijk   is there any way to do it?   thanks in advance   Regards, Harry     ... View more