topic Re: Add leading zeros to a numeric field in a proc sql connect to teradata step in SAS Enterprise Guide

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

proc_talk — Thu, 16 Jul 2020 19:10:52 GMT

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.

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

Reeza — Thu, 16 Jul 2020 19:34:23 GMT

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

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

proc_talk — Thu, 16 Jul 2020 20:01:29 GMT

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...

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

Reeza — Thu, 16 Jul 2020 20:05:24 GMT

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.

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

proc_talk — Thu, 16 Jul 2020 20:18:17 GMT

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