topic Re: Add leading zeros to numeric variable in SAS Programming

Add leading zeros to numeric variable

aga11940 — Wed, 11 Dec 2013 18:32:35 GMT

Hello, I am a SAS newbie (3 months).

In my project, I have 3 imported data sets that I need to join together on a primary key that is a numeric type. My challenge is that in two of the tables, the leading zeros do not exist, so the join will not match for those records without the leading zeros.

Example:

Table1

001001

001002

12345

54321

Table2

1001

1002

12345

54321

Table3

1001

1002

12345

54321

What can I do to add the leading zeros to the values that are missing leading zeros? The fixed length of the field is 5, so no leading zeros should be added to the values that have a non-zero number leading (e.g 12345, 10101).

Any help would be greatly appreciated!

Re: Add leading zeros to numeric variable

Reeza — Wed, 11 Dec 2013 18:35:20 GMT

Look at Z5. format.

Re: Add leading zeros to numeric variable

aga11940 — Wed, 11 Dec 2013 18:42:06 GMT

Hi Reeza, thanks for your quick response. I am not sure I follow however.

Could you please elaborate? Thank you!

Re: Add leading zeros to numeric variable

ballardw — Wed, 11 Dec 2013 18:50:56 GMT

Have you actually run the join? If so was there an error or warning message? Are the variables actually of type numeric? Your comment about fixed length of 5 makes it sound that the values are likely string containing digits.

Proc contents data=table1;run; (or what ever the actual library.dataset name for table 1 would show whether actual numeric or character)

Are you trying to do this as a pass through to another database or are the tables both SAS datasets?

Re: Add leading zeros to numeric variable

Reeza — Wed, 11 Dec 2013 18:52:36 GMT

1. Numeric variables cannot have leading zeros, so it has to be a character variable.

2. You can convert the number to a character by using the SAS format Z5.

SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition

Then you can merge the fields together on the new character field or use the conversin in your PROC SQL step.

Re: Add leading zeros to numeric variable

SteveNZ — Wed, 11 Dec 2013 18:53:30 GMT

I'd be checking that the Table 1 import hasn't made your primary key a character variable first. If it's imported it as a number it shouldn't have the leading zeroes unless you've whacked a format on it. If that's the case convert it:

newvar = input(primary_key,5.) ;

Re: Add leading zeros to numeric variable

aga11940 — Wed, 11 Dec 2013 19:19:29 GMT

Thank you everyone! I was able to resolve this by converting the number variable to character using the format Z5.

I appreciate everyones input. This was great.

Re: Add leading zeros to numeric variable

Patrick — Thu, 12 Dec 2013 08:56:44 GMT

@aga11940

Then please mark the question as answered.