Here it is. Did have to replace the names though (confidentiality issue):
Table1.naam = Partial Name belonging to an adress
Table2.naam = Full name (originally can contain ' or ", hence COMPRESS)
&SOURCETABLE = reference to source table name. (working of course)
*** The next 2 queries set up the 2 tables as wel as formats NAAM to upper case ***;
PROC SQL;
CREATE TABLE TUSSENDT.TABLE1 AS
SELECT SOURCE.GROEP,
UPCASE(SOURCE.NAAM) AS NAAM,
SOURCE.HUISNR,
SOURCE.POSTCODE
FROM &SOURCETABLE AS SOURCE
WHERE SOURCE.GROEP NOT IS NULL;
QUIT;
PROC SQL;
CREATE TABLE TUSSENDT.TABLE2 AS
SELECT UPCASE(COMPRESS(COMPRESS(TABLE2SOURCE.NAAM,''''),'"')) AS NAAM FORMAT=$200.,
TABLE2SOURCE.ID AS ID FORMAT=11.,
TABLE2SOURCE.HUISNR AS HUISNR FORMAT=$6.,
TABLE2SOURCE.HUISNRTOE AS HUISNRTOE FORMAT=$12.,
TABLE2SOURCE.POSTCODE AS POSTCODE FORMAT=$6.,
TABLE2SOURCE.BUITENLAND_IND AS BUITENLAND_IND FORMAT=$1.,
TABLE2SOURCE.BEKEND_DAT AS BEKEND_DAT FORMAT=MMDDYY8.
FROM SOURCE.TABLE2SOURCE AS TABLE2SOURCE;
QUIT;
*** Checks if TABLE2.naam contains a TABLE1.naam, adds GROEP and NAAM from TABLE1 ***;
PROC SQL;
CREATE TABLE WORK.KOI_INFO_OP_NAAM AS SELECT DISTINCT TABLE2.ID FORMAT=11.,
TABLE1.NAAM FORMAT=$200. AS NAAM_1,
TABLE2.NAAM FORMAT=$200. AS NAAM_2,
TABLE2.HUISNR FORMAT=$6.,
TABLE2.HUISNRTOE AS HUISN2 FORMAT=$12.,
TABLE2.POSTCODE AS POSTCO FORMAT=$6.,
TABLE2.BUITENLAND_IND AS BUITEN FORMAT=$1.,
TABLE2.BEKEND_DAT AS BEKEND FORMAT=MMDDYY8.,
TABLE1.GROEP,
('OP NAAM') AS OORSPRONG
FROM TUSSENDT.TABLE2 AS TABLE2,
TUSSENDT.TABLE2 AS TABLE2
WHERE TABLE2.NAAM Contains TABLE1.NAAM;
QUIT;
- As extra information: I'm using SAS 9.1.3
Message was edited by: RFLinnenbank
... View more