topic Re: Proc Import eliminating zeroes from decimal places in SAS Programming

Proc Import eliminating zeroes from decimal places

maggi2410 — Mon, 06 Aug 2012 20:12:06 GMT

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

Re: Proc Import eliminating zeroes from decimal places

SASKiwi — Mon, 06 Aug 2012 20:27:52 GMT

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.

Re: Proc Import eliminating zeroes from decimal places

maggi2410 — Mon, 06 Aug 2012 20:39:57 GMT

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?

Re: Proc Import eliminating zeroes from decimal places

Haikuo — Mon, 06 Aug 2012 21:20:30 GMT

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

Re: Proc Import eliminating zeroes from decimal places

PGStats — Mon, 06 Aug 2012 21:21:11 GMT

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;

Re: Proc Import eliminating zeroes from decimal places

maggi2410 — Mon, 06 Aug 2012 21:46:12 GMT

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

Re: Proc Import eliminating zeroes from decimal places

Haikuo — Mon, 06 Aug 2012 21:54:41 GMT

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

Haikuo

Re: Proc Import eliminating zeroes from decimal places

art297 — Mon, 06 Aug 2012 21:56:34 GMT

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.

Re: Proc Import eliminating zeroes from decimal places

Haikuo — Mon, 06 Aug 2012 22:03:26 GMT

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

Re: Proc Import eliminating zeroes from decimal places

maggi2410 — Mon, 06 Aug 2012 22:10:05 GMT

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

Re: Proc Import eliminating zeroes from decimal places

art297 — Mon, 06 Aug 2012 22:40:07 GMT

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

Re: Proc Import eliminating zeroes from decimal places

Haikuo — Mon, 06 Aug 2012 22:49:31 GMT

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

Re: Proc Import eliminating zeroes from decimal places

maggi2410 — Mon, 06 Aug 2012 22:51:03 GMT

@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 ...:(

This is the code m using

PROC IMPORT OUT= tfl

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

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

RUN;

Re: Proc Import eliminating zeroes from decimal places

art297 — Mon, 06 Aug 2012 22:53:07 GMT

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

Re: Proc Import eliminating zeroes from decimal places

Haikuo — Mon, 06 Aug 2012 22:54:47 GMT

Try this:

xxx,xxx

aaa, 12.20

bbb, 12.2

ccc,12.22

Haikuo

Re: Proc Import eliminating zeroes from decimal places

Haikuo — Mon, 06 Aug 2012 22:57:31 GMT

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

Haikuo

Re: Proc Import eliminating zeroes from decimal places

maggi2410 — Mon, 06 Aug 2012 23:00:30 GMT

I dont know why..but it still isnt working..

Re: Proc Import eliminating zeroes from decimal places

Haikuo — Mon, 06 Aug 2012 23:10:25 GMT

Getnames=NO? if using YES, then the first record will be variable names, then the mixed option will not work, as the rest of data are numeric. Or add another row of xxx,xxx .

Haikuo

Re: Proc Import eliminating zeroes from decimal places

maggi2410 — Mon, 06 Aug 2012 23:15:13 GMT

My excel already has a row for the column names. thats why i used yes.

Re: Proc Import eliminating zeroes from decimal places

Haikuo — Mon, 06 Aug 2012 23:24:54 GMT

You got me. As long as data type is mixed in the same column in the first couple of rows, and 'mixed=yes', proc import is supposed to import whatever as is in character format. It works for me on SAS 9.2, Excel 2003, winxp sp4.

Haikuo