Infile "attrib" problem - length when surfaced in table

[yzinger]

Hi folks...likely an easy question for you.

Working on a simple "infile" program and having an issue with this line:

attrib Voucher_Code length = 8 format = 30.;

...this displays as follows in SAS EG 5.1:

9.1055742547369E29

...the real number should be:

910557425474746144924007559843

...thoughts on what I am doing wrong with my attrib statement?

Thanks

[ballardw]

1. The number of digits is too large for SAS (or indeed many systems) to store as a numeric, see precision.

2. To come as close as practical SAS will use scientific notation

What arithmetic are you doing with that variable? If that is actually an account or number or such that you do not do arithemetic then store it as character of appropriate length.

[yzinger]

there is no formula involved in getting this number. It is simply passed to us in a .csv file.

This number is a voucher_code

Can you expand on the point about storing as character?

[Tom]

Do not define the field as numeric. From the field name it looks like it is just an identifier. Just because the code consists of digits does not mean that you should treat it as a number.  Change the definition of the field to character.

attrib Voucher_Code length = $30;

[yzinger]

Thank you...no format needed?

[Tom]

no format needed?

A FORMAT is instructions for how to display the value. SAS already knows how to display character strings so there is no need to permanently attach a format. Same is true for informats.  There is also a danger in permanently attaching $xx. formats to character variables.  What if you later decide to create a new dataset that uses this data, but need this variable defined longer to hold new values. If you forget to also change the permanently attached format you could have trouble viewing the data with PROC PRINT and it could even impact how the values are used in summary functions like PROC FREQ. Now if you did have a user defined format that say converted 'M' to 'Male' then there would be value in permanently attaching a format.

SAS also already knows how to display numbers so you not always need to attach formats to numbers. But with numbers SAS will default to BEST12 so if you really did have large integers (not so large that they cannot be stored like your example) then it does help to attach a format like F13. to a numeric variable. Or you might want to attach the Z format to have the integers print with leading zeros.  Or specify the number of decimal places to print.  And of course DATE, TIME and DATETIME variables are numbers and require the format to make the value appear in a human readable format.

[yzinger]

Thanks all.

[ChrisBrooks]

I agree with @Tom - my general rule of thumb is that unless I intend to do arithmetic with a number I store it as character. You'll never do arithmetic with an account code...

[yzinger]

one more quick question.

I see code that sometime has a .; and other times has just the ;

...school me please

Thanks

B

[Tom]

---

@yzinger wrote:

one more quick question.

I see code that sometime has a .; and other times has just the ;

...school me please

Thanks

B

---

For the SAS parser to know that you mean a format and not just a variable name or numeric constant you need to add the period.  That way SAS knows that this statement is attaching different formats to different variables 

format age f3. weight f6.3 ;

If you left the period off of F3. then SAS would think you meant the variable named F3 and so this statement attachs the same format to three variables.

format age f3 weight f6.3 ;

Or consider these two input statements

input name $20  ;
input name $20.;

Without the period that is saying to read NAME as the character in column 20. With the period it is saying to use formatted input to read 20 characters into NAME.

For specifying a length you do not need a period since a length can only be an integer value, but SAS will silently ignore it if you add one.  Personally I try to remember to not add the period to LENGTH settings so that I don't confuse myself.

[yzinger]

Fantastic...thanks again.

[yzinger]

oh oh...no worky:

16
17            attrib Purchase_Invoice_Id length = $30 format = $30.;
18            attrib Purchase_Invoice_Date length = 8 format=AFRDFDE9. informat=mmddyy10.;
19            attrib Vendor_ID length = $20 format = $20.;
20            attrib Internal_SKU length = $30 format = $30.;
21            attrib External_SKU length = $30 format = $30.;
22            attrib Voucher_Code length = $30;
23            attrib Issue_Date length = 8 format=AFRDFDE9. informat=mmddyy10.;
24
25              input Purchase_Invoice_Id Purchase_Invoice_Date Vendor_ID Internal_SKU External_SKU
26                    Voucher_Code Issue_Date
27         ;
28                         run;
29
30
31
31       !      proc append base=ATL_ESI.evoucher data=work.evoucher;
32         run;
WARNING: Variable Voucher_Code not appended because of type mismatch.
ERROR: No appending done because of anomalies listed above. Use FORCE option to append these files.

Thoughts?

[Tom]

You need to define it the same everywhere.  Hard to tell from your log, but it looks like you have defined it as numeric in ATL_ESI.evoucher and as character in work.evoucher.

[yzinger]

This is the entire code:

1          LIBNAME ATL_ESI BASE "/sas/sasdata/reporting/atl/esi/" ;
NOTE: Libref ATL_ESI was successfully assigned as follows:
      Engine:        BASE
      Physical Name: /sas/sasdata/reporting/atl/esi
2
3          options nonotes;
4
5          data work.evoucher;
6
7
8
9             infile '/sas/sasdata/non_standard/landing/evoucherinfile.csv'
10                   lrecl = 894
11                   delimiter = ','
12                   truncover
13                   firstobs = 1
14                   DSD
15                        ;
16
17            attrib Purchase_Invoice_Id length = $30 format = $30.;
18            attrib Purchase_Invoice_Date length = 8 format=AFRDFDE9. informat=mmddyy10.;
19            attrib Vendor_ID length = $20 format = $20.;
20            attrib Internal_SKU length = $30 format = $30.;
21            attrib External_SKU length = $30 format = $30.;
22            attrib Voucher_Code length = $30;
23            attrib Issue_Date length = 8 format=AFRDFDE9. informat=mmddyy10.;
24
25              input Purchase_Invoice_Id Purchase_Invoice_Date Vendor_ID Internal_SKU External_SKU
26                    Voucher_Code Issue_Date
27         ;
28                         run;
29
30
31
31       !      proc append base=ATL_ESI.evoucher data=work.evoucher;
32         run;

I dont know where else I could define it.

[Tom]

The libname statement is pointing to the location where the existing dataset that you are trying to append to exists.

Run a PROC CONTENTS on it and see how many observations it has and how the variables are defined.

If it does not have any data then recreate it with the new definition.

If it does have data then check the values and make sure that you do not already have some corrupted values stored.  You could possible fix the dataset and convert the variable from numeric to character (there are dozens of examples of that question on the and other SAS help sites).  

If you do have corrupted data do you still have the program(s) that created that dataset that you can fix and re-run?  

Perhaps you created it using earlier runs of this program?  PROC APPEND will create the BASE dataset from the input data if it does not already exist.  So if you don't care what is in it now you might want to use use PROC DELETE to get rid of it and then you can let PROC APPEND create a new one with the current data. That way it will definitely match the format of the new dataset.