DATA Step, Macro, Functions and more

How do I pad a character variable with leading zeroes using PROC SQL

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

How do I pad a character variable with leading zeroes using PROC SQL

I have input data that will be numeric and/or character and will be anywhere from 1 to 8 characters in length. For example;

OPTION NOCENTER;         
DATA rawdata;            
INPUT data $ 1-8;        
DATALINES;               
CCFAIL                   
JCLERROR                 
166                      
1                        
2                        
U500                     
2947                     
SE37                     
;                        
PROC PRINT DATA = rawdata;

I need the final output to be 8 characters in width, aligned right and leading zeros on any value that begin with a number extending it to a length of 8 characters. For example

JCCFAIL

JCLERROR

00000166    

00000001      

00000002      

       U500   

00002947

       SE37

 

Any thoughts on how to accomplish this would be greatly appreciated.


Accepted Solutions
Solution
‎12-28-2016 10:22 AM
Valued Guide
Posts: 797

Re: How do I pad a character variable with leading zeroes using PROC SQL

[ Edited ]

proc sql;

  select

    case (anyalpha(data)>0)

    case (notdigit(trim(data))>0)

      when (1) then right(data)

      else translate(right(data),'0',' ')

    end

    as data

  from rawdata;

quit;

 

additional edit: the TRANSLATE function translates characters in the first argument.  To me, it is counterintuitive that the 3rd arg is translated to the 2nd, but that's how it is.

View solution in original post


All Replies
Valued Guide
Posts: 797

Re: How do I pad a character variable with leading zeroes using PROC SQL

You have the character values JCLERROR and CCFAIL left-justified in the desired output, but U500 and SE37 are right-justified.  What criterion are you using to make that distinction?

Occasional Contributor
Posts: 15

Re: How do I pad a character variable with leading zeroes using PROC SQL

[ Edited ]

Looks like copy/paste got me, desired out put should all be right justified.

data   
       
       CCFAIL
 JCLERROR
    00000166
    00000001
    00000002
           U500
    00002947
           SE37

Solution
‎12-28-2016 10:22 AM
Valued Guide
Posts: 797

Re: How do I pad a character variable with leading zeroes using PROC SQL

[ Edited ]

proc sql;

  select

    case (anyalpha(data)>0)

    case (notdigit(trim(data))>0)

      when (1) then right(data)

      else translate(right(data),'0',' ')

    end

    as data

  from rawdata;

quit;

 

additional edit: the TRANSLATE function translates characters in the first argument.  To me, it is counterintuitive that the 3rd arg is translated to the 2nd, but that's how it is.

Occasional Contributor
Posts: 15

Re: How do I pad a character variable with leading zeroes using PROC SQL

Thank you!!!!

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 260 views
  • 2 likes
  • 2 in conversation