topic Re: URGENT: problem regarding decimal places being automatically changed by sas in SAS Programming

URGENT: problem regarding decimal places being automatically changed by sas

Kayla_Tan222 — Wed, 22 May 2019 07:15:19 GMT

Hi, was facing some problem regarding the decimal places.

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

zclmpd

2906

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

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;

Re: URGENT: problem regarding decimal places being automatically changed by sas

ScottBass — Tue, 21 May 2019 03:23:24 GMT

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;

Re: URGENT: problem regarding decimal places being automatically changed by sas

Kayla_Tan222 — Tue, 21 May 2019 03:35:44 GMT

Thank you ScottBass!

Re: URGENT: problem regarding decimal places being automatically changed by sas

PGStats — Tue, 21 May 2019 03:44:14 GMT

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.

Re: URGENT: problem regarding decimal places being automatically changed by sas

Kayla_Tan222 — Tue, 21 May 2019 04:16:28 GMT

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?

Re: URGENT: problem regarding decimal places being automatically changed by sas

PGStats — Tue, 21 May 2019 04:19:28 GMT

Use 16.

not 16.2

the 2 tells SAS to divide by 100.

Re: URGENT: problem regarding decimal places being automatically changed by sas

ballardw — Tue, 21 May 2019 15:20:16 GMT

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