URGENT: problem regarding decimal places being automatically changed by sas

[Kayla_Tan222]

Hi, was facing some problem regarding the decimal places.

In my excel csv format file my datas were in this format,for eg:

zclmpd

0

2906

0

6092.63

however, when I infile my excel cxv file into SAS, sas read it like this:

zclmpd

0.00

29.06    <- this is wrong

0

6092.63

how can I solve this problem?

is there any wrong with my code below?

data 201812;

%let _EFIERR_=0;

infile 'D:File\201812.csv' dlm=',' MISSOVER DSD lrecl=32767 firstobs=2;

informat zclmpd 16.2;

informat zosqs 16.2;

format zclmpd 16.2;

format zosqs 16.2;

INPUT

zclmpd

zosqs

　

if _ERROR_ then call symputx('_EFIERR_',1);

run;

[ScottBass · Accepted solution]

data test1;
   input number 16.2;
   datalines;
0
2906
0
6092.63
;
run;

data test2;
   input number best32.;
   datalines;
0
2906
0
6092.63
;
run;

[Kayla_Tan222]

Thank you ScottBass!

[PGStats]

From the documentation of the w.d informat:

Syntax
w.d
Syntax Description
w
specifies the width of the input field. 

Range 1-32 

d
specifies the power of 10 by which to divide the value. If the data contain decimal points, the d value is ignored. This argument is optional. 

Range 0-31 

So, for numbers strings NOT containing a decimal point, such as "2906", the 16.2 informat will cause the number to be divided by 100, hence the 20.06 result. Solution: use informat 16. or Best16. (synonym) instead.

[Kayla_Tan222]

Hi PG states,

I have tried to run my code with best16.2

the result with best16.2 able to get the result that I want which is 2906 compare to informat16.2 which get 29.06

I thought there is no difference between informat16.2 and best16.2, but it is not.

I have tried google search the meaning but I do not understand.

can you help me with this?

[PGStats]

Use 16.

not 16.2

the 2 tells SAS to divide by 100.

[ballardw]

---

@Kayla_Tan222 wrote:

Hi PG states,

I have tried to run my code with best16.2

the result with best16.2 able to get the result that I want which is 2906 compare to informat16.2 which get 29.06

I thought there is no difference between informat16.2 and best16.2, but it is not.

I have tried google search the meaning but I do not understand.

can you help me with this?

---

Some history about why this behavior exists.

Many years ago most data was entered for computer use using a physical card created with a keypunch machine. This card had space for a maximum of 80 characters. Since many of the numbers used for entry, and especially US finance, used a common fixed number of decimals in currency values the fields, get more data on a single card, used an implied decimal position. Suppose the first 16 characters were supposed to be a currency value and the 15th and 16th positions represented a fraction of a dollar. Then the card would be punched something like:

      123456789
             ^ implied decimal before the 8

So when you specify a decimal in reading as 16.2 then you are telling SAS to use the rules for an implied decimal, just like 40 years ago because the program has been told by you that you know the behavior of the data needs that decimal.

Some data systems still do that because of the design and they work.

I have some incoming data files that have bits like:

050519832222210000012032200211221222999999991211201065499992132162999919209999-121439990409201013699912212010030120089910409201099000001022100001566179

Which is about 50 variables, some of which have implied decimals. So it is nice that I can read the data directly and get the decimals where they need to be.

So if I specify 16.2 I am telling SAS "that variable needs two decimals. If there is not one in the actual text read, supply it".

If I specify an informat of 16. I am telling SAS "read up to 16 digits and use any decimals when present otherwise the value is an integer"

data junk;
   input x :16.  y :16.2;
datalines;
123       123
123.456   123.456
123E4     123E4
;
run;