CODEWITHSUNDEEP
sas
juhee Chung
juhee Chung

Reputation: 13

How to sort address alphabetically in SAS?

I have a dataset that has bunch of addresses. 

PROC SORT DATA=work68;
    by ADDRESS ;


run;

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

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

Upvotes: 0

Views: 1253

Answers (3)

Richard
Richard

Reputation: 27508

Proc SQL syntax can sort data in special ways, 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

Upvotes: 0

user667489
user667489

Reputation: 9569

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;

Upvotes: 0

Joe
Joe

Reputation: 63424

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

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

Upvotes: 1

Related Questions