@Mohan2020 wrote:
Thanks a ton for your help. it worked.
i just found one more issue, while converting from .xls to sas , i found barcode has values like ‘6.50E+11 ‘ for few records in excel spreadsheet and when It was converted to SAS dataset , the barcode value has leading zeros like '65234300000' , pl let me know how i can fix it.
You need to say a lot more about what you actually have to get help. You mentioned leading zeros but don't show any values with leading zeros. You don't explain whether the variable is numeric or character in the SAS dataset. You don't explain whether all of the cells in the column in XLS are numeric or not. SAS and Excel will both default to displaying large numbers using the scientific notation you mentioned. The way a number is displayed does not change the way it is stored.
That might also be related to have a trashed XLS file. For example if you use Excel to open a text file (like a CSV file) it will by default guess how to interpret each value. If it sees a value that looks like a number it will make it into a number. So leading zeros will disappear as they have no meaning in a number. Or it might think that a string with / or - in it is a date instead of a string.
To have more control when reading the data into SAS it might help to have Excel write a CSV file and then read that file using a data step. But if the data is already corrupted in the XLS file (either by Excel's import issue or by user error) then that might not solve all of the problems.
... View more