DATA Step, Macro, Functions and more

add leading zero for a variable with both char and num

Accepted Solution Solved
Reply
Super Contributor
Posts: 336
Accepted Solution

add leading zero for a variable with both char and num

I have saw many questions similar but after read those posts I still need to ask.

My question:

there is a variable has around 100 values, from 8, 9, 111, 23, 456,457, ab1, ac2, abc9, d388, q34, s789, etc

 

I want to sort it to a way that I feel comfortable with, that is, it should sfirst start with, 8, then 9, then 23... then ab1, ac2, then d388...

the rule would be:

 

if all are numbers, the order should be from small to big, that is, 8 should be listed before 111;

those with letters will be after the numbers, and in the order with alphabit order, that is, after these numbers, ab1 should be first,

 

but even I sort it, it still lists 111 before 8. I then realize it might be because sas considers 1 is small then 8.

I thought I could add leading zero, I do not know how to do this becasue it is a mixture of numeric and alpha.

Thank you in advance for your advice and time!

 


Accepted Solutions
Solution
‎06-14-2017 04:37 PM
Respected Advisor
Posts: 3,124

Re: add leading zero for a variable with both char and num

Proc Sort has built-in features just what you want:

 

data have;
infile cards dlm=',';
input var$ @@;
cards;
 8, 9, 111, 23, 456,457, ab1, ac2, abc9, d388, q34, s789,etc
 ;

 proc sort data=have out=want sortseq=linguistic(NUMERIC_COLLATION=on) ;
 by var;
 run;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,615

Re: add leading zero for a variable with both char and num

You could use the ANYALPHA function to determine if the text has alphabetic characters or not. If there are no alphabetic characters, then you can concatenate zeros in front of the numeric.

Solution
‎06-14-2017 04:37 PM
Respected Advisor
Posts: 3,124

Re: add leading zero for a variable with both char and num

Proc Sort has built-in features just what you want:

 

data have;
infile cards dlm=',';
input var$ @@;
cards;
 8, 9, 111, 23, 456,457, ab1, ac2, abc9, d388, q34, s789,etc
 ;

 proc sort data=have out=want sortseq=linguistic(NUMERIC_COLLATION=on) ;
 by var;
 run;
Super User
Posts: 5,082

Re: add leading zero for a variable with both char and num

If it's acceptable to add a variable just for sorting purposes, this might be easier than adding leading zeros:

 

if input(varname, ??8.) > . then sorting_var = right(varname);

else sorting_var = varname;

 

Then sort by SORTING_VAR instead of by the original variable name.

Contributor
Posts: 30

Re: add leading zero for a variable with both char and num

You have numeric values and character values mixed in one variable, which means that the data type of the variable is char, and in sorting SAS does not see the numeric values as numbers, but sort them as strings. you can get the wanted result by splitting the variable in 2, the first a string variable with (or without) the letters, and the second a numeric variable with the numbers, and then sort by both. there are many methods for doing that, one is the following; data have; input value $char8.; datalines; ab1 8 111 d388 9 456 23 abc9 457 ac2 ; data wrk; set have; length c $8 n $8; c = prxchange('s/(\D*)(\d*)/$1/',-1,trim(value)); n = input(prxchange('s/(\D*)(\d*)/$2/',-1,trim(value)),8.); run; proc sort data=wrk out=want(drop=c n); by c n; run; data _null_; set want; put value; run; Result: 8 9 23 111 456 457 ab1 abc9 ac2 d388
Contributor
Posts: 30

Re: add leading zero for a variable with both char and num

Sorry the formatting was so bad.

 

data have;

input value $char8.;

datalines;

ab1

8

111

d388

9

456

23

abc9

457

ac2

;

data wrk; set have;

      length c $8 n $8;

      c = prxchange('s/(\D*)(\d*)/$1/',-1,trim(value));

      n = input(prxchange('s/(\D*)(\d*)/$2/',-1,trim(value)),8.);

run;

proc sort data=wrk out=want(drop=c n); by c n;

run;

 

data _null_; set want; put value;

run;

 

Result:

8
9
23
111
456
457
ab1
abc9
ac2
d388

 

Super User
Posts: 10,500

Re: add leading zero for a variable with both char and num

Look up the PROC Sort options SORTSEQ with LINGUISTIC options. Learning that proc sort has these options may save you many problems in the future so I'm not providing an example.

 

Note that AFAIK only Proc Sort will create the order properly (though if you have mixed case letters there are still issues). Other procedures that produce output may or maynot maintain your desired order.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 150 views
  • 3 likes
  • 6 in conversation