- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 06-14-2016 05:39 AM
(4734 views)
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
???
6 REPLIES 6
Diamond | Level 26
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Without know what each of the possible combinations are and what they mean, I am not sure you can. You could drop all non-numeric data: compress(<var>,' ','kd')
But this probably wont help, I mean what is: 21/121/4 and how does it compare to Top 21 3 G for instance, there doesn't seem to be anything logical in that data at all.
Barite | Level 11
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Suppose you have a single address string
HAVE
ENROLLMENT GROUP 39 E COCONUT ST 3RD FL
WANT
[
(u'ENROLLMENT', 'Recipient'),
(u'GROUP', 'Recipient'),
(u'39', 'AddressNumber'),
(u'E', 'StreetNamePreDirectional'),
(u'COCONUT', 'StreetName'),
(u'ST', 'StreetNamePostType'),
(u'3RD', 'OccupancyIdentifier'),
(u'FL\n', 'OccupancyType')
]
* the Python USADDRESS package can do much more than
this. But lets see if it can parse the 6 permutations
of the same address below. I have already used PERL
to do some translations , ie East to E, Street to ST, THIRD to 3rd
floor to FL.
* create input for Python;
data adr_badadr;
file "d:/txt/KeyBadAdr.txt";
length keybadadr $100.;
input;
key=put(10000000000+_n_,z11.);
KeyBadAdr=cats(key,_infile_);
putlog KeyBadAdr;
put KeyBadAdr;
cards4;
ENROLLMENT GROUP 39 E COCONUT ST 3RD FL
ENROLLMENT GROUP 3RD FL 39 E COCONUT ST
39 E COCONUT ST ENROLLMENT GROUP 3RD FL
39 E COCONUT ST 3RD FL ENROLLMENT GROUP
3RD FL ENROLLMENT GROUP 39 E COCONUT ST
3RD FL 39 E COCONUT ST ENROLLMENT GROUP
;;;;
;run;quit;
/* in the log
10000000001ENROLLMENT GROUP 39 E COCONUT ST 3RD FL
10000000002ENROLLMENT GROUP 3RD FL 39 E COCONUT ST
1000000000339 E COCONUT ST ENROLLMENT GROUP 3RD FL
1000000000439 E COCONUT ST 3RD FL ENROLLMENT GROUP
100000000053RD FL ENROLLMENT GROUP 39 E COCONUT ST
100000000063RD FL 39 E COCONUT ST ENROLLMENT GROUP
*/
* It looks like you will have to do a lot of manual
cleaning before you can use open source packages
to clean stadardize your addresses.
* the most critical address parts for a strong matchcode are
street number
street name
Note it is not really possible to know if
'Enrollment' is a recipient or placename
Group could be a state or locality like UTAH,
depending on its position.
Also it is not always possible to get the
occupancy parts. It depends on the location of the occupancy parts.
You can really mess up a mailing with assumptions
on non street parts.
I am going to build a matchcode based on
First character of first name, last name, street number, street name
and zipcode.
Python USADDRESS does an excellent job of parsing the
the key elements for a match code, ie Street Number and Street Name. It also handles components like PO BOX.
* here is the simple python code that takes your input
and creates the output above.
%utl_submit_py64(%nrbquote(
fo = open('d:/txt/KeyBadAdrFix.txt', 'w');
import usaddress;
for line in open('d:/txt/KeyBadAdr.txt'):;
. addr=line[11:];
. key=line[0:11];
. res=key + str(usaddress.parse(addr)) + '\n';
. lyn=str(addr);
. fo.write(str(res));
));
* good way to look the python output, you need full SAS for this.
proc fslist file="d:/txt/KeyBadAdrFix.txt";
run;quit;
* here is the parsing of the first address by Python
10000000001[
(u'ENROLLMENT', 'Recipient'),
(u'GROUP', 'Recipient'),
(u'39', 'AddressNumber'),
(u'E', 'StreetNamePreDirectional'),
(u'COCONUT', 'StreetName'),
(u'ST', 'StreetNamePostType'),
(u'3RD', 'OccupancyIdentifier'),
(u'FL\n', 'OccupancyType')]
Here is the last record
10000000006[
(u'3RD', 'OccupancyIdentifier'),
(u'FL', 'OccupancyType'),
(u'39', 'AddressNumber'),
(u'E', 'StreetNamePreDirectional'),
(u'COCONUT', 'StreetName'),
(u'ST', 'StreetNamePostType'),
(u'ENROLLMENT', 'PlaceName'),
(u'GROUP\n', 'StateName')
* lets see if all of these addressses have the same
street number
street name
* create name value pairs for further processing;
data KeyBadAdrNrm;
retain seq 0 adrrec name value;
keep seq adrrec name value;
length txt $100 key $11 str $700;
infile "d:/txt/KeyBadAdrFix.txt" lrecl=1034;
input;
adrrec=substr(_infile_,1,11);
parens=countc(_infile_,')');
str=substr(_infile_,12);
do i=1 to parens;
txt=substr(scan(str,i,')'),3);
txt=compress(txt,"'[()'");
txt=substr(prxchange('s/\\n//',2,txt),2);
seq=seq+1;
value=scan(txt,1,',');
name=scan(txt,2,',');
output;
end;
run;quit;
p to 40 obs WORK.KEYBADADRNRM total obs=48
ENROLLMENT GROUP 39 E COCONUT ST 3RD FL
bs SEQ ADRREC NAME VALUE
1 1 10000000001 Recipient ENROLLMENT
2 2 10000000001 Recipient GROUP
3 3 10000000001 AddressNumber 39
4 4 10000000001 StreetNamePreDirectional E
5 5 10000000001 StreetName COCONUT
6 6 10000000001 StreetNamePostType ST
7 7 10000000001 OccupancyIdentifier 3RD
8 8 10000000001 OccupancyType FL
9 9 10000000002 Recipient ENROLLMENT
10 10 10000000002 Recipient GROUP
11 11 10000000002 Recipient 3RD
12 12 10000000002 Recipient FL
13 13 10000000002 AddressNumber 39
14 14 10000000002 StreetNamePreDirectional E
15 15 10000000002 StreetName COCONUT
16 16 10000000002 StreetNamePostType ST If you check you will see all addresses had street number and street name parsed correctly.Other elements depend on position in string, no way around this.
* you need to upcase, remove punctuation and
convert to standard USPS abreviations.
Perl has some free translations.
CAUSEWAY CSWY
CAUSWAY CSWY
CASWAY CSWY
CEN CTR
CENT CTR
CENTER CTR
CENTERS CTRS
CENTR CTR
CENTRE CTR
CIRC CIR
CIRCL CIR
CIRCLE CIR
CIRCLES CIRS
;
* invoking python from SAS;
%macro utl_submit_py64(pgm)/des="Semi colon separated set of py commands";
* write the program to a temporary file;
filename py_pgm "%sysfunc(pathname(work))/py_pgm.py" lrecl=32766 recfm=v;
data _null_;
length pgm $32755 cmd $255;
file py_pgm ;
pgm="&pgm";
semi=countc(pgm,';');
do idx=1 to semi;
cmd=cats(scan(pgm,idx,';'));
if cmd=:'.' then cmd=substr(cmd,2);
put cmd $char96.;
putlog cmd $char96.;
end;
run;
%let _loc=%sysfunc(pathname(py_pgm));
%put &_loc;
filename rut pipe "C:\Python_27_64bit/python.exe &_loc";
data _null_;
file print;
infile rut;
input;
put _infile_;
run;
filename rut clear;
filename py_pgm clear;
%mend utl_submit_py64;
Calcite | Level 5
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hello Roger,
First, allow me to say thank you for your reply and the time invested in writing the code.
Secondy, I am completely new to SAS, without background in Perl or Python. Threfore, I would really apprecite if you could send me detailed description of how to integrate your code in SAS Data Job (I suppose, that is what needs to be done). Additionally, I would like to mention that I already have QKB, the German version.
Regards!
Barite | Level 11
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Suppose you have a single address string
HAVE
ENROLLMENT GROUP 39 E COCONUT ST 3RD FL
WANT
[
(u'ENROLLMENT', 'Recipient'),
(u'GROUP', 'Recipient'),
(u'39', 'AddressNumber'),
(u'E', 'StreetNamePreDirectional'),
(u'COCONUT', 'StreetName'),
(u'ST', 'StreetNamePostType'),
(u'3RD', 'OccupancyIdentifier'),
(u'FL\n', 'OccupancyType')
]
* the Python USADDRESS package can do much more than
this. But lets see if it can parse the 6 permutations
of the same address below. I have already used PERL
to do some translations , ie East to E, Street to ST, THIRD to 3rd
floor to FL.
* create input for Python;
data adr_badadr;
file "d:/txt/KeyBadAdr.txt";
length keybadadr $100.;
input;
key=put(10000000000+_n_,z11.);
KeyBadAdr=cats(key,_infile_);
putlog KeyBadAdr;
put KeyBadAdr;
cards4;
ENROLLMENT GROUP 39 E COCONUT ST 3RD FL
ENROLLMENT GROUP 3RD FL 39 E COCONUT ST
39 E COCONUT ST ENROLLMENT GROUP 3RD FL
39 E COCONUT ST 3RD FL ENROLLMENT GROUP
3RD FL ENROLLMENT GROUP 39 E COCONUT ST
3RD FL 39 E COCONUT ST ENROLLMENT GROUP
;;;;
;run;quit;
/* in the log
10000000001ENROLLMENT GROUP 39 E COCONUT ST 3RD FL
10000000002ENROLLMENT GROUP 3RD FL 39 E COCONUT ST
1000000000339 E COCONUT ST ENROLLMENT GROUP 3RD FL
1000000000439 E COCONUT ST 3RD FL ENROLLMENT GROUP
100000000053RD FL ENROLLMENT GROUP 39 E COCONUT ST
100000000063RD FL 39 E COCONUT ST ENROLLMENT GROUP
*/
* It looks like you will have to do a lot of manual
cleaning before you can use open source packages
to clean stadardize your addresses.
* the most critical address parts for a strong matchcode are
street number
street name
Note it is not really possible to know if
'Enrollment' is a recipient or placename
Group could be a state or locality like UTAH,
depending on its position.
Also it is not always possible to get the
occupancy parts. It depends on the location of the occupancy parts.
You can really mess up a mailing with assumptions
on non street parts.
I am going to build a matchcode based on
First character of first name, last name, street number, street name
and zipcode.
Python USADDRESS does an excellent job of parsing the
the key elements for a match code, ie Street Number and Street Name. It also handles components like PO BOX.
* here is the simple python code that takes your input
and creates the output above.
%utl_submit_py64(%nrbquote(
fo = open('d:/txt/KeyBadAdrFix.txt', 'w');
import usaddress;
for line in open('d:/txt/KeyBadAdr.txt'):;
. addr=line[11:];
. key=line[0:11];
. res=key + str(usaddress.parse(addr)) + '\n';
. lyn=str(addr);
. fo.write(str(res));
));
* good way to look the python output, you need full SAS for this.
proc fslist file="d:/txt/KeyBadAdrFix.txt";
run;quit;
* here is the parsing of the first address by Python
10000000001[
(u'ENROLLMENT', 'Recipient'),
(u'GROUP', 'Recipient'),
(u'39', 'AddressNumber'),
(u'E', 'StreetNamePreDirectional'),
(u'COCONUT', 'StreetName'),
(u'ST', 'StreetNamePostType'),
(u'3RD', 'OccupancyIdentifier'),
(u'FL\n', 'OccupancyType')]
Here is the last record
10000000006[
(u'3RD', 'OccupancyIdentifier'),
(u'FL', 'OccupancyType'),
(u'39', 'AddressNumber'),
(u'E', 'StreetNamePreDirectional'),
(u'COCONUT', 'StreetName'),
(u'ST', 'StreetNamePostType'),
(u'ENROLLMENT', 'PlaceName'),
(u'GROUP\n', 'StateName')
* lets see if all of these addressses have the same
street number
street name
* create name value pairs for further processing;
data KeyBadAdrNrm;
retain seq 0 adrrec name value;
keep seq adrrec name value;
length txt $100 key $11 str $700;
infile "d:/txt/KeyBadAdrFix.txt" lrecl=1034;
input;
adrrec=substr(_infile_,1,11);
parens=countc(_infile_,')');
str=substr(_infile_,12);
do i=1 to parens;
txt=substr(scan(str,i,')'),3);
txt=compress(txt,"'[()'");
txt=substr(prxchange('s/\\n//',2,txt),2);
seq=seq+1;
value=scan(txt,1,',');
name=scan(txt,2,',');
output;
end;
run;quit;
p to 40 obs WORK.KEYBADADRNRM total obs=48
ENROLLMENT GROUP 39 E COCONUT ST 3RD FL
bs SEQ ADRREC NAME VALUE
1 1 10000000001 Recipient ENROLLMENT
2 2 10000000001 Recipient GROUP
3 3 10000000001 AddressNumber 39
4 4 10000000001 StreetNamePreDirectional E
5 5 10000000001 StreetName COCONUT
6 6 10000000001 StreetNamePostType ST
7 7 10000000001 OccupancyIdentifier 3RD
8 8 10000000001 OccupancyType FL
9 9 10000000002 Recipient ENROLLMENT
10 10 10000000002 Recipient GROUP
11 11 10000000002 Recipient 3RD
12 12 10000000002 Recipient FL
13 13 10000000002 AddressNumber 39
14 14 10000000002 StreetNamePreDirectional E
15 15 10000000002 StreetName COCONUT
16 16 10000000002 StreetNamePostType ST If you check you will see all addresses had street number and street name parsed correctly.Other elements depend on position in string, no way around this.
* you need to upcase, remove punctuation and
convert to standard USPS abreviations.
Perl has some free translations.
CAUSEWAY CSWY
CAUSWAY CSWY
CASWAY CSWY
CEN CTR
CENT CTR
CENTER CTR
CENTERS CTRS
CENTR CTR
CENTRE CTR
CIRC CIR
CIRCL CIR
CIRCLE CIR
CIRCLES CIRS
;
* invoking python from SAS;
%macro utl_submit_py64(pgm)/des="Semi colon separated set of py commands";
* write the program to a temporary file;
filename py_pgm "%sysfunc(pathname(work))/py_pgm.py" lrecl=32766 recfm=v;
data _null_;
length pgm $32755 cmd $255;
file py_pgm ;
pgm="&pgm";
semi=countc(pgm,';');
do idx=1 to semi;
cmd=cats(scan(pgm,idx,';'));
if cmd=:'.' then cmd=substr(cmd,2);
put cmd $char96.;
putlog cmd $char96.;
end;
run;
%let _loc=%sysfunc(pathname(py_pgm));
%put &_loc;
filename rut pipe "C:\Python_27_64bit/python.exe &_loc";
data _null_;
file print;
infile rut;
input;
put _infile_;
run;
filename rut clear;
filename py_pgm clear;
%mend utl_submit_py64;
Barite | Level 11
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
This code should help you with initial cleanup. Just put the location of pythin in the utl_submit_p64get pythonhttps://www.python.org/downloads/You do not need to know much Python to exceute the USADDRESS package.There are excellent eduacational materials on the web.libname adr "d:/adr";
options fmtsearch=(adr.adr_formats_v1 work.formats);
*************************************************************************;
data adr.&pgm._unitabv;
retain fmtname "$adr_unit2abv";
informat start $12. label $5.;
input start label;
end=start;
cards4;
APARTMENT APT
BASEMENT BSMT
BUILDING BLDG
DEPARTMENT DEPT
FLOOR FL
FRONT FRNT
HANGAR HNGR
LOBBY LBBY
LOT LOT
LOWER LOWR
OFFICE OFC
PENTHOUSE PH
PIER PIER
REAR REAR
ROOM RM
SIDE SIDE
SLIP SLIP
SPACE SPC
STOP STOP
SUITE STE
TRAILER TRLR
UNIT UNIT
UPPER UPPR
;;;;
;run;quit;
data adr.&pgm._abv2stdes;
retain fmtname "$adr_abv2stdes";
informat start $7. label $16.;
input start label;
end=start;
cards4;
ALY ALLEY
ANX ANNEX
APT APARTMENT
ARC ARCADE
AVE AVENUE
BSMT BASEMENT
BYU BAYOU
BCH BEACH
BND BEND
BLF BLUFF
BTM BOTTOM
BLVD BOULEVARD
BR BRANCH
BRG BRIDGE
BRK BROOK
BLDG BUILDING
BG BURG
BYP BYPASS
CP CAMP
CYN CANYON
CPE CAPE
CSWY CAUSEWAY
CTR CENTER
CIR CIRCLE
CLF CLIFF
CLFS CLIFFS
CLB CLUB
COR CORNER
CORS CORNERS
CRSE COURSE
CT COURT
CTS COURTS
CV COVE
CRK CREEK
CRES CRESCENT
XING CROSSING
DL DALE
DM DAM
DEPT DEPARTMENT
DV DIVIDE
DR DRIVE
EST ESTATE
EXPY EXPRESSWAY
EXT EXTENSION
FLS FALLS
FRY FERRY
FLD FIELD
FLDS FIELDS
FLT FLAT
FL FLOOR
FRD FORD
FRST FOREST
FRG FORGE
FRK FORK
FRKS FORKS
FT FORT
FWY FREEWAY
FRNT FRONT
GDN GARDEN
GDNS GARDENS
GTWY GATEWAY
GLN GLEN
GRN GREEN
GRV GROVE
HNGR HANGER
HBR HARBOR
HVN HAVEN
HTS HEIGHTS
HWY HIGHWAY
HL HILL
HLS HILLS
HOLW HOLLOW
INLT INLET
IS ISLAND
ISS ISLANDS
JCT JUNCTION
KY KEY
KNL KNOLL
KNLS KNOLLS
LK LAKE
LKS LAKES
LNDG LANDING
LN LANE
LGT LIGHT
LF LOAF
LBBY LOBBY
LCK LOCK
LCKS LOCKS
LDG LODGE
LOWR LOWER
MNR MANOR
MDW MEADOW
MDWS MEADOWS
ML MILL
MLS MILLS
MSN MISSION
MT MOUNT
MTN MOUNTAIN
NCK NECK
OFC OFFICE
ORCH ORCHARD
PKWY PARKWAY
PH PENTHOUSE
PNE PINE
PNES PINES
PL PLACE
PLN PLAIN
PLNS PLAINS
PLZ PLAZA
PT POINT
PRT PORT
PR PRAIRIE
RADL RADIAL
RNCH RANCH
RPD RAPID
RPDS RAPIDS
RST REST
RDG RIDGE
RIV RIVER
RD ROAD
RM ROOM
SHL SHOAL
SHLS SHOALS
SHR SHORE
SHRS SHORES
SPC SPACE
SPG SPRING
SPGS SPRINGS
SQ SQUARE
STA STATION
STRA STRAVENUE
STRM STREAM
ST STREET
STE SUITE
SMT SUMMIT
TER TERRACE
TRCE TRACE
TRAK TRACK
TRFY TRAFFICWAY
TRL TRAIL
TRLR TRAILER
TUNL TUNNEL
TPKE TURNPIKE
UN UNION
UPPR UPPER
VLY VALLEY
VIA VIADUCT
VW VIEW
VLG VILLAGE
VL VILLE
VIS VISTA
WAY WAY
WL WELL
WLS WELLS
;;;;
;run;quit;
proc format cntlin=adr.&pgm._abv2stdes lib=adr.adr_formats_v1;
run;quit;
data adr.&pgm._nrmst;
input;
_infile_=upcase(_infile_);
fro=scan(_infile_,1,' ');
too=scan(_infile_,2,' ');
if substr(strip(fro),-1,1)='S' and
substr(strip(too),-1,1)='S' then too=substr(too,1,length(too)-1);
cards4;
ALLEY ALY
ANEX ANX
ANNEX ANX
ARCADE ARC
BEACH BCH
BEND BND
BLUF BLF
BOTTOM BTM
BRANCH BR
BRIDGE BRG
BRNCH BR
BROOK BRK
BROOKS BRKS
BURG BG
BURGS BGS
BYPA BYP
BYPAS BYP
BYPASS BYP
BYPS BYP
CAMP CP
CANYN CYN
CANYON CYN
CAPE CPE
CAUSEWAY CSWY
CAUSWAY CSWY
CEN CTR
CENT CTR
CENTER CTR
CENTERS CTRS
CENTR CTR
CENTRE CTR
CIRC CIR
CIRCL CIR
CIRCLE CIR
CIRCLES CIRS
CK CRK
CLIFF CLF
CLIFFS CLFS
CLUB CLB
CMP CP
CNTER CTR
CNTR CTR
CNYN CYN
COMMON CMN
CORNER COR
CORNERS CORS
COURSE CRSE
COURT CT
COURTS CTS
COVE CV
COVES CVS
CR CRK
CRCL CIR
CRCLE CIR
CRECENT CRES
CREEK CRK
CRESCENT CRES
CRESENT CRES
CREST CRST
CROSSING XING
CROSSROAD XRD
CRSCNT CRES
CRSENT CRES
CRSNT CRES
CRSSING XING
CRSSNG XING
CRT CT
CURVE CURV
DALE DL
DAM DM
DIV DV
DIVIDE DV
DRIV DR
DRIVE DR
DRIVES DRS
DRV DR
DVD DV
ESTATE EST
ESTATES ESTS
EXP EXPY
EXPR EXPY
EXPRESS EXPY
EXPRESSWAY EXPY
EXPW EXPY
EXTENSION EXT
EXTENSIONS EXTS
EXTN EXT
EXTNSN EXT
FALLS FLS
FERRY FRY
FIELD FLD
FIELDS FLDS
FLAT FLT
FLATS FLTS
FORD FRD
FORDS FRDS
FOREST FRST
FORESTS FRST
FORG FRG
FORGE FRG
FORGES FRGS
FORK FRK
FORKS FRKS
FORT FT
FREEWAY FWY
FREEWY FWY
FRRY FRY
FRT FT
FRWAY FWY
FRWY FWY
GARDEN GDN
GARDENS GDNS
GARDN GDN
GATEWAY GTWY
GATEWY GTWY
GATWAY GTWY
GLEN GLN
GLENS GLNS
GRDEN GDN
GRDN GDN
GRDNS GDNS
GREEN GRN
GREENS GRNS
GROV GRV
GROVE GRV
GROVES GRVS
GTWAY GTWY
HARB HBR
HARBOR HBR
HARBORS HBRS
HARBR HBR
HAVEN HVN
HAVN HVN
HEIGHT HTS
HEIGHTS HTS
HGTS HTS
HIGHWAY HWY
HIGHWY HWY
HILL HL
HILLS HLS
HIWAY HWY
HIWY HWY
HLLW HOLW
HOLLOW HOLW
HOLLOWS HOLW
HOLWS HOLW
HRBOR HBR
HT HTS
HWAY HWY
INLET INLT
ISLAND IS
ISLANDS ISS
ISLES ISLE
ISLND IS
ISLNDS ISS
JCTION JCT
JCTN JCT
JCTNS JCTS
JUNCTION JCT
JUNCTIONS JCTS
JUNCTN JCT
JUNCTON JCT
KEY KY
KEYS KYS
KNOL KNL
KNOLL KNL
KNOLLS KNLS
LA LN
LAKE LK
LAKES LKS
LANDING LNDG
LANE LN
LANES LN
LDGE LDG
LIGHT LGT
LIGHTS LGTS
LNDNG LNDG
LOAF LF
LOCK LCK
LOCKS LCKS
LODG LDG
LODGE LDG
LOOPS LOOP
MANOR MNR
MANORS MNRS
MEADOW MDW
MEADOWS MDWS
MEDOWS MDWS
MILL ML
MILLS MLS
MISSION MSN
MISSN MSN
MNT MT
MNTAIN MTN
MNTN MTN
MNTNS MTNS
MOTORWAY MTWY
MOUNT MT
MOUNTAIN MTN
MOUNTAINS MTNS
MOUNTIN MTN
MSSN MSN
MTIN MTN
NECK NCK
ORCHARD ORCH
ORCHRD ORCH
OVERPASS OPAS
OVL OVAL
PARKS PARK
PARKWAY PKWY
PARKWAYS PKWY
PARKWY PKWY
PASSAGE PSGE
PATHS PATH
PIKES PIKE
PINE PNE
PINES PNES
PK PARK
PKWAY PKWY
PKWYS PKWY
PKY PKWY
PLACE PL
PLAIN PLN
PLAINES PLNS
PLAINS PLNS
PLAZA PLZ
PLZA PLZ
POINT PT
POINTS PTS
PORT PRT
PORTS PRTS
PRAIRIE PR
PRARIE PR
PRK PARK
PRR PR
RAD RADL
RADIAL RADL
RADIEL RADL
RANCH RNCH
RANCHES RNCH
RAPID RPD
RAPIDS RPDS
RDGE RDG
REST RST
RIDGE RDG
RIDGES RDGS
RIVER RIV
RIVR RIV
RNCHS RNCH
ROAD RD
ROADS RDS
ROUTE RTE
RVR RIV
SHOAL SHL
SHOALS SHLS
SHOAR SHR
SHOARS SHRS
SHORE SHR
SHORES SHRS
SKYWAY SKWY
SPNG SPG
SPNGS SPGS
SPRING SPG
SPRINGS SPGS
SPRNG SPG
SPRNGS SPGS
SPURS SPUR
SQR SQ
SQRE SQ
SQRS SQS
SQU SQ
SQUARE SQ
SQUARES SQS
STATION STA
STATN STA
STN STA
STR ST
STRAV STRA
STRAVE STRA
STRAVEN STRA
STRAVENUE STRA
STRAVN STRA
STREAM STRM
STREET ST
STREETS STS
STREME STRM
STRT ST
STRVN STRA
STRVNUE STRA
SUMIT SMT
SUMITT SMT
SUMMIT SMT
TERR TER
TERRACE TER
THROUGHWAY TRWY
TPK TPKE
TR TRL
TRACE TRCE
TRACES TRCE
TRACK TRAK
TRACKS TRAK
TRAFFICWAY TRFY
TRAIL TRL
TRAILS TRL
TRK TRAK
TRKS TRAK
TRLS TRL
TRNPK TPKE
TRPK TPKE
TUNEL TUNL
TUNLS TUNL
TUNNEL TUNL
TUNNELS TUNL
TUNNL TUNL
TURNPIKE TPKE
TURNPK TPKE
UNDERPASS UPAS
UNION UN
UNIONS UNS
VALLEY VLY
VALLEYS VLYS
VALLY VLY
VDCT VIA
VIADCT VIA
VIADUCT VIA
VIEW VW
VIEWS VWS
VILL VLG
VILLAG VLG
VILLAGE VLG
VILLAGES VLGS
VILLE VL
VILLG VLG
VILLIAGE VLG
VIST VIS
VISTA VIS
VLLY VLY
VST VIS
VSTA VIS
WALKS WALK
WELL WL
WELLS WLS
WY WAY
;;;;
;run;quit;
data adr.&pgm._fmtmkesfx;
retain fmtname '$adr_st2sfx';
set adr.&pgm._nrmst;
start=fro;
end=start;
label=too;
;run;quit;
proc format cntlin=adr.&pgm._fmtmkesfx lib=adr.adr_formats_v1;
;run;quit;
proc format cntlin=adr.&pgm._unitabv lib=adr.adr_formats_v1;
;run;quit;
proc format cntlin=adr.&pgm._abv2stdes lib=adr.adr_formats_v1;
;run;quit;
proc catalog cat=adr.adr_formats_v1;
modify ADR_ABV2STDES.formatc(description="Reverse street short form to long form TPKE->Turnpike");
modify ADR_UNIT2ABV.formatc(description="Occupancy Type ie APARTMENT -> APT");
modify ADR_ST2SFX.formatc(description="Street suffix ie TURNPIKE -> TPKE");
;run;quit;
proc catalog cat=adr.adr_formats_v1;
contents;
;run;quit;
Calcite | Level 5
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you for the quick reply. I will try to do it that way.
In the meantime, would you please have a look at the following 2 posts, which are connected to the same topic:
and
https://communities.sas.com/t5/SAS-Data-Management/Matching-Wrong-Rows-From-Two-Tables/m-p/278204
Looking forward to your reply.
Regards!
