Help using Base SAS procedures

Keeping leading 0 while converting from character to numeric

Accepted Solution Solved
Reply
Regular Contributor
Posts: 189
Accepted Solution

Keeping leading 0 while converting from character to numeric

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..

 

column A.JPG


Accepted Solutions
Solution
‎04-25-2018 10:53 PM
Super User
Posts: 23,774

Re: Keeping leading 0 while converting from character to numeric

Posted in reply to Ashwini_uci

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?


 

View solution in original post


All Replies
Super User
Posts: 23,774

Re: Keeping leading 0 while converting from character to numeric

Posted in reply to Ashwini_uci
Z8.2
Regular Contributor
Posts: 189

Re: Keeping leading 0 while converting from character to numeric

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?

Super User
Posts: 13,583

Re: Keeping leading 0 while converting from character to numeric

Posted in reply to Ashwini_uci

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

 

Regular Contributor
Posts: 189

Re: Keeping leading 0 while converting from character to numeric

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.

 

Super User
Posts: 23,774

Re: Keeping leading 0 while converting from character to numeric

Posted in reply to Ashwini_uci

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.

 


 

Super User
Posts: 6,785

Re: Keeping leading 0 while converting from character to numeric

Posted in reply to Ashwini_uci

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.

Regular Contributor
Posts: 189

Re: Keeping leading 0 while converting from character to numeric

Posted in reply to Astounding

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.

Solution
‎04-25-2018 10:53 PM
Super User
Posts: 23,774

Re: Keeping leading 0 while converting from character to numeric

Posted in reply to Ashwini_uci

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?


 

Regular Contributor
Posts: 189

Re: Keeping leading 0 while converting from character to numeric

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!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 409 views
  • 6 likes
  • 4 in conversation