DATA Step, Macro, Functions and more

Infile "attrib" problem - length when surfaced in table

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Infile "attrib" problem - length when surfaced in table

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


Accepted Solutions
Solution
‎09-25-2017 09:54 AM
Occasional Contributor
Posts: 10

Re: Infile "attrib" problem - length when surfaced in table

Hi guys...just wanted to explain how I ended up fixing the problem.

 

I changed the Voucher Code value to:

 

attrib Voucher_Code length = $30;

 

as suggested by you guys. However, I was then getting the errors you see above. What I did was clear the table of all data, then I run the top part of my code changing:

 

data work.evoucher

 

to

 

data atl_esi.evoucher

 

..this will recreate the table with my new attrib above. Note I left out the proc append from end of my original code.

 

Once this hacked code completed I checked the table and it loaded properly. I then was able to run the entire code (with atl_esi.evoucher changed back to work.evoucher and with the proc append statement) and again table loaded fine and log indicated no errors.

 

So, im summary I had to recreate the table perse so as to make sure my work.evoucher table (session type table) matched my target table.

 

Thanks for all help folks.

B

 

View solution in original post


All Replies
Super User
Posts: 13,941

Re: Infile "attrib" problem - length when surfaced in table

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.

 

Occasional Contributor
Posts: 10

Re: Infile "attrib" problem - length when surfaced in table

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?

Super User
Super User
Posts: 8,279

Re: Infile "attrib" problem - length when surfaced in table

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;

 

 

Occasional Contributor
Posts: 10

Re: Infile "attrib" problem - length when surfaced in table

Thank you...no format needed?

Super User
Super User
Posts: 8,279

Re: Infile "attrib" problem - length when surfaced in table

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.

Occasional Contributor
Posts: 10

Re: Infile "attrib" problem - length when surfaced in table

Thanks all.

 

Valued Guide
Posts: 596

Re: Infile "attrib" problem - length when surfaced in table

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

Occasional Contributor
Posts: 10

Re: Infile "attrib" problem - length when surfaced in table

Posted in reply to ChrisBrooks

one more quick question.

 

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

 

...school me please

 

Thanks

B

Super User
Super User
Posts: 8,279

Re: Infile "attrib" problem - length when surfaced in table


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.

Occasional Contributor
Posts: 10

Re: Infile "attrib" problem - length when surfaced in table

Fantastic...thanks again.

Occasional Contributor
Posts: 10

Re: Infile "attrib" problem - length when surfaced in table

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?

Super User
Super User
Posts: 8,279

Re: Infile "attrib" problem - length when surfaced in table

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.

Occasional Contributor
Posts: 10

Re: Infile "attrib" problem - length when surfaced in table

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.

Super User
Super User
Posts: 8,279

Re: Infile "attrib" problem - length when surfaced in table

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.

 

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 17 replies
  • 178 views
  • 3 likes
  • 4 in conversation