Hi all,              I have hashed account numbers that I am querying against a database. I cannot accommodate the length of a macro. Could you please help me with that?    I have used the following code to generate a sample dataset. This is dummy data. I am attaching error that I am getting while running the code.  data ValList (drop=ValueList); do ValueList = 100 to 1000000 by 100; valuenum=put(ValueList,6.); output; end; run; /*method1*/ proc sql noprint; select "'"||strip(Valuenum)||"'" into :SearchMacro separated by ', ' from ValList; quit; /* method2*/ %macro test; %let dsid=%sysfunc(open(ValList)); %let cnt=%sysfunc(attrn(&dsid,nobs)); %do i=1 %to &cnt; %let rc=%sysfunc(fetchobs(&dsid,&i)); %cmpres(%sysfunc(getvarc(&dsid,%sysfunc(varnum(&dsid,Valuenum))))) %end; %let rc=%sysfunc(close(&dsid)); %mend test; %put %test;  The second method was copied from  https://support.sas.com/kb/39/605.html  It works well for the shorter numbers (for a macro), but I don't know how to give quotes and commas to separate from one number to the other. Any help with that code is much appreciated.    Thanks in advance, Buddha. ... View more

Fellow SAS users,                    I have some messy data and I cleaned it up little bit. I have account dataset which has account number (acct_nbr) and account holder name (acct_name). Acct_Name column has messy customer name information (Since multiple words of first, last and middle names are mixed up in random order). I have another dataset which has customer name information. Customer_name has only customer name (first, middle and last names). I want to join both datasets by matching name and if a letter or word extra from acct_name shouldn't be matched.  proc sql; create table WORK.have1( bufsize=65536 ) ( acct_nbr char(150) format=$150. informat=$150. label='acct_number', Acct_NAME char(150) ); insert into WORK.have1 values('54321', 'M A F VENKATA SHIVA SAI ARUN KUMARI KANDARPA B S RA' ) values('54321', 'VENKATA SHIVA SAI M A F ARUN KUMARI KANDARPA B S RA' ) values('54321', 'VENKATA SHIVA SAI M A F ARUN B S RA KUMARI KANDARPA' ) values('54321', 'VENKATA SHIVA SAI M A F ARUN B S RA KUMARI KANDARPA INCORPORATED' ) values('54321', 'VENKATA SHIVA SAI M A F ARUN KUMARI KANDARPA B S RA LLC' ) values('1234AUC5','V V S MUDIMI HARINATH PEMBARTHY BABU VADA VEMULA' ) values('1234AUC5','V V S VEMULA MUDIMI PEMBARTHY VADA BABU HARINATH' ) values('1234AUC5','VEMULA MUDIMI PEMBARTHY V V S VADA BABU HARINATH' ) values('1234AUC5','VEMULA MUDIMI AND PEMBARTHY V V S VADA BABU HARINATH' ) values('1234AUC5','A B V V S MUDIMI HARINATH PEMBARTHY BABU VADA VEMULA' ) values('12534AUC5','GARCIA DELYNN CHRISTINE DE DE LEON LA VEGA FOX TENA PONCE ' ) values('12534AUC5','GARCIA DE LA VEGA DELYNN CHRISTINE FOX TENA PONCE DE LEON' ) values('12534AUC5','GARCIA DE LA VEGA TENA PONCE DE LEON DELYNN CHRISTINE FOX ' ) values('A1234AUC5','ELAINE ALLAN CHRISTOPHER ELLIOTT LAA BELLE' ) values('A1234AUC5','CHRISTOPHER ELLIOTT LAA BELLE ELAINE ALLAN' ) values('1234AUC5','ALLAN CHRISTOPHER ELLIOTT ABIGAIL NICOLE K CATHARINE DA RE' ) values('1234AUC5','DA RE NICOLE K CATHARINE ELLIOTT ABIGAIL ALLAN CHRISTOPHER' ) values('1234AUC5',' RE DA CATHARINE K NICOLE ABIGAIL ELLIOTT CHRISTOPHER ALLAN' ) values('54689AGC','WONG TONY') values('54689AGC','WONG K TONY') values('54689AGC','TONY WONG JAY ') ; quit; proc sql; create table WORK.have2( bufsize=65536 ) ( first_name char(150), middle_name char(150), last_name char(150) ); insert into WORK.have2 values('VENKATA SHIVA SAI ARUN KUMARI', 'M A F', 'KANDARPA B S RA' ) values('V V S HARINATH BABU', 'PEMBARTHY MUDIMI', 'VEMULA VADA' ) values('GARCIA DE LA VEGA', 'DELYNN CHRISTINE FOX', 'TENA PONCE DE LEON' ) values('ELAINE', 'ALLAN CHRISTOPHER ELLIOTT', 'LAA BELLE' ) values('ALLAN CHRISTOPHER ELLIOTT', 'ABIGAIL NICOLE K', 'CATHARINE DA RE' ) values('TONY','','WONG') ; quit; The output should have             Please check the attachment for the result output.    I tried different ways to join, but unsuccessful. Any help would be appreciated. The issue is the two joining string columns are not arranged in a proper way to join. The most important thing to remember is to match acct_name with first_name, middle_name and last_name fields (in any order). If account name (for example WONG K TONY) is different from (TONY WONG) as there is an extra K in the string.  Please help me here or can give suggestions too.  Thanks in Advance.      ... View more