I am working with 2 tables: TBL_source and TBL_AdressMaster. after I join these 2 tables by using matching codes, and cluster them, I want to select the surviving record. For that purpose I define this expression in the surviving record element:   if Not isnull(`S_Door`) and compare(`AM_DOOR_NUMBER_CASED_MATCHCODE`, 'S_DOOR_CASED_MATCHCODE', true) == 0 then return true else if isnull(`S_Door`) and compare(`AM_DOOR_NUMBER_CASED`, 'UNKNOWN', true) == 0 then return true else return false the idea is: when the door_numer is NULL and when there is match between source and adress master, in regard to door_number, then it should return 0. When the door_numer is unknown, than it should mark exactly one row in the adress master which coinains the value "unknown" in the door_number field (this is to avoid large clusters). Otherwise, return false.   The second part of the expression (door_number IS NULL) seems to work as expected. The first part returns unexpeted results: although there is a record in the adress_master with exactly the same door_number as in the source table, the software matches BUT DOES NOT MARK as the  surviving record the correct row from the adressmaster.   Can anyone explain why? Did I define the expression correctly? Or do I need to do something else? ... View more

In the first table I have defined field 'TBL_1_Door_Number' as TEXT, Sensitivity=85. In the second table I have defined field "TBL_2_Door_Number" the same way as in the first table.   After that I am generating matching codes based on those fields and joining the result in one table. Although the definition is same these two fields from different tables generate DIFFERENT MATCHING CODES for SAME values. For example, the text value '5' in the first table generates the matching code 'H$$$$$$$$$$$$$$' and the same text value in the second table generates the matching code '5$$$$$$$$$$$$$$'.   Can anyone give me an explanation why the software generates different matching codes for the same values? ... View more

I need somebody who can help me clean my "Contracts" table. My approach can be seen in the data job (attachment)   The biggest probem is with extracting the correct HOUSE_NUMBER and DOOR_NUMBER fields (which are "melted" into STREET field)   For example Housenumber can have values such as: Top112 21/121/4 Block 23 Room 443 Weg6 Top4- E32 Top 21 3 OG Top 212/ Terminal 2 Object 21 Street 32g (   Out of 150.000 distinct contracts around 50.000 have unmatchable House Numbers.   How can I correctly parse the fields to extract: Street House Number Door Number ???   ... View more

I have a column "House number" which besides numbers contains also something like this "32/2/1" or "Top 211". The column is defined as TEXT. My question is: how can I extract only first number from the string such as ""32/2/1"? (the expression needs to correctly extract the number when only a number stands in the field such as "32").     ... View more

Could you please have a look at the data job I created.  The goal is to match adressess from CONFIG_CONTRACTXML table with the address master table (AT_address_export). (I am completely new to SAS and am using it for around 10 day. So, the help and expert feedback would be very appreciated).   In particular, I would like to know is the definition in Data Union well made. The lowest level in the address hierarchy is the 'Door' level. In the node 'Data Union' I joined the 2 data sources to the lowest level (door). Is that correct? or should I go to 'House Number'?   Are Matching codes well defined?  (a) in regard to the definition of Heuse_Number, Stairway and Door is it correct to take 'Address' as definition?  (b) when doing the matching, is it suggested to go to the lowest level (door)? Or maybe House Number would be better?   Are clusters well/reasonable defined? Surviving record as well?   I would also need the help with interpreting the final result: Contract_IDs in the CONFIG_CONTRACTXML table can show up multiple times (they are not unique). What do I need to do to make a statement about how SAS improved the data quality with this job? Help me, help you sell SAS Software 🙂  I am using SAS Data Mgmt Studio 2.7 And this is a crutial point, because the output I see is a table which contains the number of rows equal to the sum of the number of rows of two data sources. The big question is: if the adressmatch before was around 65%, to which extent did SAS help improve the address matching? How can I evaluate that based on the end result (the one table on MS SQL Server)? What is the percentage increase in matching after using SAS software?   Looking forward to more detailed feedback.   Regards!   UPDATE: 8th, June, 2016 I have updated the Data Union node, where I made the join to the lowest address level - door (see the picture in the attachment). I do not understand why I only hava NULL values in the final result. Can anybody explain this please? ... View more

When defining the 'House Number', 'Stairway' and 'Door' in the Match Codes node, which definition has to be selected? Is that 'Address'? Which precision is set by the rule of thumb (before using SAS Studio the match between adressess was between 60 and 70%) See the attachment. I am using SAS Data Mgmt Studio 2.7 ... View more

I Have parsed the field "Street" and as a result I got threea additional fields: "Street Name, House Number, Extension". However, I already had the fields "Street_NAME" and "HOUSE_NUM". What I would like to do is to make a third column which will be populated in in a way that first the values from the "Street_NAME" column are taken, and if that value does not exist, the value from the parsed columnd "treet Name" is taken. Same principle should be applied to House Number as well.   How, and with which nodes can I make this? ... View more