I am trying to import an excel file into SAS using SAS EG Import wizard. I have a field X with type char4. But, it has numeric data. e.g. 1101 During import, SAS truncates leading zeros although the data type is character. Is there a way to retain the leading zeros. I cannot append zeros in SAS because there are few values which are legitimate 3 digits and few which have become 3 digits due to leading zero truncation. So, hard to figure out which values should be padded with leading zero.
0
votes
Is the field a character field in Excel? In that case SAS shouldn't truncate your values as long as you have set the input format to character and length to 4. If it's not a character field in excel you shouldn't have leading zeroes in Excel either.
- Seba
In excel, it has leading zeros available. I assume it means it is general/ text.
- Kunal Batra
If it's general/text SAS might have a hard time understanding that it's a character if there is only numbers in the excel file. If you either change it to text and re-import or in the 3rd screen in the import wizard set the type to character, in-format to $char4., length to 4, and out-format to $char4. you should be ok.
- Seba
I initially had tried the Wizard option but it did not work. But after explicitly converting the excel format to text instead of general, it worked.
- Kunal Batra
That's great! Usually it's enough to only explicitly convert the first field to text and SAS will get what's going on.
- Seba
1 Answers
1
votes
If it's general/text SAS might have a hard time understanding that it's a character if there is only numbers in the excel file. You should have two ways of making it understand that the field is a pure text field.
- If you either change it to text and re-import
- In the 3rd screen in the import wizard set the type to character, in-format to $char4., length to 4, and out-format to $char4. you should be ok.
(Post my comment as an answer so it can be closed)
