Keeping leading 0 while converting from character to numeric

[Ashwini_uci]

Hi,

I use SAS enterprise. While importing a comma delimited excel -csv file into SAS, I want to convert the first column (A) from character filed to numeric field without losing the leading zeros.

I selected w.d and bestw.d formats from the drop-down menu but the leading zeros were not retained.

1. Is there any other format i can select to serve this purpose.
2. I also want to get rid of the single quotation marks from the values under the columns.

Any ideas/ suggestions/help are greatly appreciated!

I am attaching the sample of the file here..

[Reeza]

Z8.2

[Ashwini_uci]

Thank you Reeza.

It is actually showing me all blanks after conversion because the values carry the single quotation marks. Is there any way i can get rid of those before conversion from character to numeric?

[ballardw]

It may help to describe, preferably with code, how you imported the data.

[Ashwini_uci]

I am using SAS enterprise and I am using the import data option from File menu and following the prompts.

The data is being imported fine. But when I try to convert from character to numeric, it shows all misisng values and I think it is because of the quotation marks around the values. I am not sure how i can get rid of the quotation marks. They disappear when I choose the numeric option during the import process. In that case the quotation marks go away but the values end up missing the leading zeros as well.

Hope this description helps.

[Reeza]

If the data was imported 'fine' it wouldn't have quotation marks. That's not a typical way to store data. 

---

@Ashwini_uci wrote:

I am using SAS enterprise and I am using the import data option from File menu and following the prompts.

The data is being imported fine. But when I try to convert from character to numeric, it shows all misisng values and I think it is because of the quotation marks around the values. I am not sure how i can get rid of the quotation marks. They disappear when I choose the numeric option during the import process. In that case the quotation marks go away but the values end up missing the leading zeros as well.

Hope this description helps.

---

[Astounding]

In passing, you mentioned the right solution.

Import the field as numeric.

Then apply a format:

format a z5.;

You can't store the variable with a leading zero, because numbers are not stored as a string of characters.  But the format lets you print the numeric value with a leading zero.

[Ashwini_uci]

Thank you @Astounding!

That is what I was hoping to achieve, but the drop-down list does not know z5 format. So I ended up importing the data as is. Then i used the compress fucntion to get rid of the quotations marks first followed by converting the format to z5. It finally worked.

[Reeza · Accepted solution]

Did you check the list of functions available in SAS?

They're listed here for the record:

http://documentation.sas.com/?docsetId=lefunctionsref&docsetTarget=n01f5qrjoh9h4hn1olbdpb5pr2td.htm&...

You probably want to look at COMPRESS() or TRANSLATE().

---

@Ashwini_uci wrote:

Thank you Reeza.

It is actually showing me all blanks after conversion because the values carry the single quotation marks. Is there any way i can get rid of those before conversion from character to numeric?

---

[Ashwini_uci]

Thank you @Reeza!

Compress function worked fine in getting rid of the quotations marks. Then I was able to convert the format to z5. It all finally worked.

Thank you!