BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ashwini_uci
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

9 REPLIES 9
Ashwini_uci
Obsidian | Level 7

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
Super User

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

 

Ashwini_uci
Obsidian | Level 7

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
Super User

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
PROC Star

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
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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