Add leading zeros to a numeric field in a proc sql connect to teradata step

[proc_talk]

Hello SAS Community,

Is there a way to pull a numeric field in a "proc sql; connect to teradata" step and add leading zeros at the same time? So I'd want to make this sort of translation:

Have ----> Want

1    ----->     01

10  ----->     10

11  ----->     11

12  ----->     12

2    ----->     02

20  ----->     20

21  ----->     21

22  ----->     22

I feel like I've been dancing around the solution using one of the cast, input, or put functions and the format of char(2), char2., z2., 'XX', or $char2. but I just can't seem to get the right combination to get it to work. I've tried all of the commented out combinations in the code below. 

proc sql;
connect to teradata;
create table add_leading_zeros as select * from connection to teradata
( select distinct
/*     want_character = input(have_number,char2.)*/
/*     want_character = put(have_number,char2.)*/
/*     want_character = put(have_number,char(2))*/
/*     want_character = put(have_number,z2.)*/
/*     cast(have_number as z2.)*/
/*     cast(have_number as char('XX'))*/
/*     cast(have_number as char(2))*/
/*     cast(have_number as char2.)*/
/*     cast(have_number as $char2.)*/
/*     cast(have_number as '99')*/
/*     have_number format $char2.*/
/*     have_number format char2.*/
/*     have_number format CHAR(2)*/
/*     format(have_number,CHAR(2))*/
/*     put(have_number,CHAR(2)) as want_character*/
/*     input(have_number,CHAR(2)) as want_character*/
from database1.table1
order by 1
);
disconnect from teradata;
quit;

I'm using SAS Enterprise Guide 9.4.

[Reeza]

You're using pass through sql so you need to use a teradata function not a SAS function. I think it's lpad there.


lpad(have_number, 2, '0')

https://docs.teradata.com/reader/kmuOwjp1zEYg98JsB8fu_A/e5w8LujIQDlVmRSww2E27A

[proc_talk]

Thanks @Reeza! Your suggestion seems like what I'm after and it doesn't throw an error like all the other options I tried, however there is no change to the values, i.e. "1" is still "1" not "01". That documentation says that the source string should be character string or expression, do you think I need to nest changing the numeric values into a character string within the LPAD function? I'm not sure how to do that either...

[Reeza]

Search the page I linked above for a function to convert your number to a character. You tried CAST above and it's likely that.

[proc_talk · Accepted solution]

Looks like casting as a VARCHAR does the trick! Thanks for the nudge in the right direction @Reeza!

LPAD(cast(have_number as varchar(2)), 2, '0') as want_character