BookmarkSubscribeRSS Feed
maggi2410
Obsidian | Level 7

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

25 REPLIES 25
SASKiwi
PROC Star

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.

maggi2410
Obsidian | Level 7

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?

Haikuo
Onyx | Level 15

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

PGStats
Opal | Level 21

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

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

Haikuo
Onyx | Level 15

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

Haikuo

art297
Opal | Level 21

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.

Haikuo
Onyx | Level 15

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

maggi2410
Obsidian | Level 7

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

art297
Opal | Level 21

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

Haikuo
Onyx | Level 15

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

art297
Opal | Level 21

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

Haikuo
Onyx | Level 15

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

Haikuo

maggi2410
Obsidian | Level 7

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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 25 replies
  • 4357 views
  • 0 likes
  • 5 in conversation