How to sort address alphabetically in SAS?

I have a dataset that has bunch of addresses.

dataset

PROC SORT DATA=work68;
by ADDRESS ;


run;


However it only show ADDRESS columns like .. it considers only the very first number of address..

ADDRESS

2237 Strang Avenue
2932 Ely Avenue
3306 Wilson Ave
3313 Wilson Avenue
3313 Wilson Avenue
3313 Wilson Avenue
46 Nuvern Avenue


3 Answers
3

You can use the option SORTSEQ=LINGUISTIC(NUMERIC_COLLATION=ON) to ask SAS to try and sort numeric values as if they were numbers.

SORTSEQ=LINGUISTIC(NUMERIC_COLLATION=ON)

PROC SORT DATA=work68 sortseq=linguistic(numeric_collation=on);
by ADDRESS ;
run;


If I understand correctly what you're asking, you could try creating a new address column with all digits removed and sort on that:

data have;
input address $100.;
infile cards truncover;
cards;
1107 Huichton Rd.
1111 Ely Avenue
;
run;

data v_have /view = v_have;
set have;
address_nonumbers = strip(compress(address,,'d'));
run;

proc sort data = v_have out = want;
by address_nonumbers;
run;


Proc SQL syntax can sort data in special ways, ORDER BY <computation-1>, …, <computation-N>

ORDER BY <computation-1>, …, <computation-N>

You may want to sort by street names first, and then by numeric premise identifier (house number). For example

Data

data have; input; address=_infile_;datalines;
2237 Strang Avenue
2932 Ely Avenue
3306 Wilson Ave
3313 Wilson Avenue
46 Nuvern Avenue
3313 Ely Avenue
4494 Nuvern Avenue
run;


Sort on street name, then house number

proc sql;
create table want as
select *
from have
order by
compress (address,,'ds') /* ignore digits and spaces - presume to be street name */
, input (scan(address,1),? best12.) /* house number */
;
quit;


This example has simplified presumptions and will not properly sort address constructs such as #### ##th Street

#### ##th Street

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.