topic Re: Keeping leading 0 while converting from character to numeric in SAS Procedures

Keeping leading 0 while converting from character to numeric

Ashwini_uci — Wed, 25 Apr 2018 22:03:12 GMT

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.

Is there any other format i can select to serve this purpose.
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..

Re: Keeping leading 0 while converting from character to numeric

Reeza — Wed, 25 Apr 2018 22:12:26 GMT

Z8.2

Re: Keeping leading 0 while converting from character to numeric

Ashwini_uci — Wed, 25 Apr 2018 22:29:04 GMT

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?

Re: Keeping leading 0 while converting from character to numeric

ballardw — Wed, 25 Apr 2018 22:36:26 GMT

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

Re: Keeping leading 0 while converting from character to numeric

Reeza — Wed, 25 Apr 2018 22:41:56 GMT

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&docsetVersion=9.4&locale=en

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?

Re: Keeping leading 0 while converting from character to numeric

Ashwini_uci — Wed, 25 Apr 2018 22:42:22 GMT

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.

Re: Keeping leading 0 while converting from character to numeric

Reeza — Wed, 25 Apr 2018 22:48:28 GMT

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.

Re: Keeping leading 0 while converting from character to numeric

Astounding — Wed, 25 Apr 2018 23:14:12 GMT

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.

Re: Keeping leading 0 while converting from character to numeric

Ashwini_uci — Thu, 26 Apr 2018 02:51:27 GMT

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.

Re: Keeping leading 0 while converting from character to numeric

Ashwini_uci — Thu, 26 Apr 2018 02:53:03 GMT

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!