DATA Step, Macro, Functions and more

Proc Import eliminating zeroes from decimal places

Reply
Contributor
Posts: 43

Proc Import eliminating zeroes from decimal places

I am importing an excel first  to sas dataset .When I am importing(using PROC IMPORT) it, the excel which has values like 5.10, 5.20,2.22 ;in SAS dataset they are converted to 5.1,5.2, 2.22 resp.

I dont want the zeroes from decimal places to be removed by SAS while importing to sas dataset. How can I do that?

Thanks!!

Megha

Super User
Posts: 3,101

Re: Proc Import eliminating zeroes from decimal places

SAS does not 'remove' zeroes when importing data, but may apply a default format to imported data if none is supplied such as BEST12. This format has the effect of not displaying trailing zeroes for decimal places. Try assinging a different format to your numeric variables like 12.2 to retain zeroes in decimal places.

Contributor
Posts: 43

Re: Proc Import eliminating zeroes from decimal places

I cannot change the excel. May be I need to use the data step version of Prioc import and tweak it then? Do anyone think of anyother way to do so?

Respected Advisor
Posts: 3,124

Re: Proc Import eliminating zeroes from decimal places

Proc import does things by itself (fully automated), I don't think you can do anything about it regarding this matter. Since you are open to data step approach, here is another way:

filename tt 'c:\test\test.txt';  /*change to your path and file*/

libname exl excel 'c:\test\test.xls'; /*change to your path and file*/

/*if you need to do something using SAS tables, otherwise skip next 2 data step*/

data want;

set exl.'sheet1$'n;

format var 5.2;

run;

data _null_;

file tt;

set want;

put var;

run;

/*the direct path from excel to txt*/

data _null_;

file tt;

set exl.'sheet1$'n;

put var 5.2;

run;

Haikuo

Respected Advisor
Posts: 4,641

Re: Proc Import eliminating zeroes from decimal places

LIBNAME offers you more flexibility. You could use something like:

libname xl Excel "myFile.xls";

data myDataset;

set xl.'Sheet1$'n;

format myVar 12.2;

run;

libname xl clear;

PG

PG
Contributor
Posts: 43

Re: Proc Import eliminating zeroes from decimal places

Thanks everybody for your answers.

Buy my data can have values like 12.20, 12.22 and 12.1 as well. If its just 12.1, then I dont want it to be converted to 12.10 (by adding the format 5.2) or something . Even best5.2 doesn't work

Respected Advisor
Posts: 3,124

Re: Proc Import eliminating zeroes from decimal places

Looks like to me that the only option left is to convert the variable to character. Have you tried that?

Haikuo

PROC Star
Posts: 7,357

Re: Proc Import eliminating zeroes from decimal places

Then I think you have to use a pure datastep approach where you read the excel data for that field as character.  I know of two ways to do that.  One, a paper that a group of us presented at SGF this year.  You can find it, and its code, by doing a Google search for copy and paste almost anything.  The code is at the hit you will receive from that search that is located at sascommunity.org.

Alternatively, you can make a copy of the spreadsheet, add one extra record (which you will delete after the import) that is the same as the first record but contains a string like xxx for that field, and use the mixed option.

Respected Advisor
Posts: 3,124

Re: Proc Import eliminating zeroes from decimal places

Art, your paper is too deep for me :smileysilly:. I had it open for 2 weeks, still hasn't finished it. But thank you very much, this will push me to learn!

Haikuo

Contributor
Posts: 43

Re: Proc Import eliminating zeroes from decimal places

Thanks Art. Yes I guess I will take a lot of time too for reading the paper.

Thanks Haikuo. But converting into cahracter also doesnt work

May be I will start exploring ods tagsets then. I guess it could help

PROC Star
Posts: 7,357

Re: Proc Import eliminating zeroes from decimal places

@Haikuo: The nice thing about the code presented in that paper is that one doesn't have to be able to understand the code in order to make good use of it.  However, that said, understanding the code gives one the benefit of being able to improve its functionality.  I, personally, have improved the code for a re-presentation of it I've been asked to do next month at the MWSUG meeting, and am just now looking at a further expansion of the idea for an invited paper at next year's SGF.

Regardless, honestly, I think the easiest way for the current OP to solve the problem is with the modified workbook as I described in my last post to this thread.

Respected Advisor
Posts: 3,124

Re: Proc Import eliminating zeroes from decimal places

I agree. That is why I don't understand OP's notion that converting to character did not work. OP may not be able to modify directly on the original excel file, while making a copy and then convert the variable to character seems doable to me. Besides, I don't think ODS is relevant in this context, as you still have to read it into SAS to be able to use ODS, and it is the very readingin process that knocks off the zeros.

Haikuo

PROC Star
Posts: 7,357

Re: Proc Import eliminating zeroes from decimal places

@Haikuo: Not converting into character but, rather, importing as character in the first place.

Respected Advisor
Posts: 3,124

Re: Proc Import eliminating zeroes from decimal places

Right, I took something for granted, again. Converting to character will take off the trailing zeros. Thanks for correcting me, Art.

Haikuo

Contributor
Posts: 43

Re: Proc Import eliminating zeroes from decimal places

@Art: If you mean something as shown below: If my excel has three records like this :

      ,xxx(extra record)

aaa, 12.20

bbb, 12.2

ccc,12.22

This also doesnt work for me ...Smiley Sad

This is the code m using

PROC IMPORT OUT= tfl

     DATAFILE= "./docs/tnf.xls" DBMS=XLS REPLACE;

     sheet="tfl";  GETNAMES=YES;  MIXED=YES;

RUN;

Ask a Question
Discussion stats
  • 25 replies
  • 1459 views
  • 0 likes
  • 5 in conversation