08-06-2012 04:12 PM
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?
08-06-2012 04:27 PM
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.
08-06-2012 04:39 PM
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?
08-06-2012 05:20 PM
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*/
format var 5.2;
/*the direct path from excel to txt*/
put var 5.2;
08-06-2012 05:21 PM
LIBNAME offers you more flexibility. You could use something like:
libname xl Excel "myFile.xls";
format myVar 12.2;
libname xl clear;
08-06-2012 05:46 PM
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
08-06-2012 05:56 PM
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.
08-06-2012 06:03 PM
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!
08-06-2012 06:10 PM
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
08-06-2012 06:40 PM
@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.
08-06-2012 06:49 PM
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.
08-06-2012 06:57 PM
Right, I took something for granted, again. Converting to character will take off the trailing zeros. Thanks for correcting me, Art.
08-06-2012 06:51 PM
@Art: If you mean something as shown below: If my excel has three records like this :
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;