topic Re: Leading zeros in New SAS User

Leading zeros

marleeakerson — Wed, 15 Jul 2020 16:06:15 GMT

Hello,

I need some help with important an excel document where some observations have leading zeros and some do not. For example, take the following sample dataset:

ID Variable

1 002

2 617

3 012

4 G317

How do I import this into sas while keeping the leading zeros?

Thank you!

Re: Leading zeros

PaigeMiller — Wed, 15 Jul 2020 16:19:02 GMT

Are you using PROC IMPORT? It guesses, and sometimes it guesses wrong, and thus you can't specifically control the import and have it always behave the way you want.

If you can convert the Excel file to .csv, then you can read it in using custom data step code, and force this column to be character and then the leading zeros will be retained.

Re: Leading zeros

ballardw — Wed, 15 Jul 2020 16:18:23 GMT

Have you tried reading the data already? If you have values like G317 as shown I would expect Proc Import to read the column as text. You may want to use the SCANTEXT option to make sure the length set holds all the values.

Or how did you expect to read the file?

Re: Leading zeros

andreas_lds — Thu, 16 Jul 2020 05:38:37 GMT

I may depend on how the column "variable" is defined in excel. If it is configured to be text, proc import reads it as text retaining the leading zeros.

EDIT: Forgot to mention: it is really time to post the code you have used to read the file.

Re: Leading zeros

Kurt_Bremser — Thu, 16 Jul 2020 09:00:16 GMT

Please attach an example of this Excel file to your next post.

Re: Leading zeros

Tom — Thu, 16 Jul 2020 20:58:11 GMT

If you are really using PROC IMPORT to read from an XLSX (or even an XLS) file then SAS will NOT remove the leading zeros. If they are gone then that was caused in Excel. Most likely the cells with the missing leading zeros have mistakenly been created a numeric values in the Excel sheet instead of the strings that they actually are.