Invalid data, datalines on SAS BI server

Accepted Solution Solved
Reply
Super Contributor
Posts: 307
Accepted Solution

Invalid data, datalines on SAS BI server

Anyone with thoughts on this issue?

I am creating a very simple dataset that works flawlessly in SAS 9.3 and EG 4.3. However, the code returns "invalid data" notes when run on the SAS application server.

The four variables are all numeric, no big surprises. Here is a partial sample of the code:

DATA wages ;

INPUT wageno awe_wage adjustment ;

format derived_annual_wage dollar12.0 ;

if adjustment = . then derived_annual_wage = round ( awe_wage * 52 , 10. ) ;

else derived_annual_wage = round ( awe_wage * adjustment * 52 , 10. ) ;

DATALINES;

101 1537.98 .

102 1537.98 .

103 1537.98 .

104 1537.98 .

105 569.84 .

106 1642.96 0.93

107 1053.6 .

. . .  more datalines . . .

;

run;

As indicated in my opening statement, this code executes without any errors or notes in SAS 9.3/EG 4.3 and will incorporate the adjustment (when present) and correctly calculate "derived_annual_wage".

When running the same code in the BI environment (SASApp server), SAS seems to be unable to correctly interpret the variables. The derived_annual_wage does not calculate correctly and the output is often terminated. I have tried many different solutions all to no avail. Sample note below . . .

NOTE: Invalid data for adjustment in line 527 13-14.

RULE:      ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0                                                                                   

527 CHAR   101 1537.98 ..                                                                

ZONE   33323333233220222222222222222222222222222222222222222222222222222222222222222222

NUMR   10101537E980ED000000000000000000000000000000000000000000000000000000000000000000

wageno=101 awe_wage=1537.98 adjustment=. derived_annual_wage=$79,970 _ERROR_=1 _N_=1

NOTE: Invalid data for adjustment in line 528 13-14.


Accepted Solutions
Solution
‎05-10-2013 09:50 PM
Super Contributor
Posts: 644

Re: Invalid data, datalines on SAS BI server

This might work, using the translate function to modify the carriage return to a space.

Richard

DATA wages ;

INPUT @ ;

_Infile_ = translate (_Infile_, '20'x, '0D'x) ;

INPUT wageno awe_wage adjustment ;

format derived_annual_wage dollar12.0 ;

if adjustment = . then derived_annual_wage = round ( awe_wage * 52 , 10. ) ;

else derived_annual_wage = round ( awe_wage * adjustment * 52 , 10. ) ;

DATALINES;

101 1537.98 .

102 1537.98 .

103 1537.98 .

104 1537.98 .

105 569.84 .

106 1642.96 0.93

107 1053.6 .

. . .  more datalines . . .

;

run;


View solution in original post


All Replies
PROC Star
Posts: 1,227

Re: Invalid data, datalines on SAS BI server

What does the data on line 527 look like?  If you delete that row of data, does it work?

Maybe look at that data in a hex editor, to make sure you don't have non-printable characters in there or something funky like that.  Can also check that the CR/LF are consistent.

Super Contributor
Posts: 307

Re: Invalid data, datalines on SAS BI server

SAS issues notes for all the data lines. The line I provided was just a sample.

As you suggest, I have already checked for non-printable characters, text characters, etc. All appears ok.

PROC Star
Posts: 1,227

Re: Invalid data, datalines on SAS BI server

Every line, eh?

Looking at the dump from your error message, looks like your lines end with just a Carriage Return (0D). 

Depending on the OS of your app server, it could be expecting a CR/LF, or a LF.  Looks to me like the CR is being read as if it was data, and that is causing it to choke.  Might be able to test this by reading just the first 2 vars, and see if that works.  Or just change a few records to be lined up in columns, and read them in using column input.  If you can read them in using column input, I think that would be evidence that the CR at the end is the problem.

Super Contributor
Posts: 307

Re: Invalid data, datalines on SAS BI server

Thanks, Quentin. I'm going to take a fresh look at this on Monday. Your suggestion is certainly putting me on the right track I think. Part of the problem is that I am writing the code/text file on a Windows machine, and then running the code on a SAS server running on a UNIX box. I've tried setting the TERMSTR option on an INFILE statement but SAS doesn't like that either.

Solution
‎05-10-2013 09:50 PM
Super Contributor
Posts: 644

Re: Invalid data, datalines on SAS BI server

This might work, using the translate function to modify the carriage return to a space.

Richard

DATA wages ;

INPUT @ ;

_Infile_ = translate (_Infile_, '20'x, '0D'x) ;

INPUT wageno awe_wage adjustment ;

format derived_annual_wage dollar12.0 ;

if adjustment = . then derived_annual_wage = round ( awe_wage * 52 , 10. ) ;

else derived_annual_wage = round ( awe_wage * adjustment * 52 , 10. ) ;

DATALINES;

101 1537.98 .

102 1537.98 .

103 1537.98 .

104 1537.98 .

105 569.84 .

106 1642.96 0.93

107 1053.6 .

. . .  more datalines . . .

;

run;


Super Contributor
Posts: 307

Re: Invalid data, datalines on SAS BI server

Thank you everyone for your help.

That solution worked, Richard. Thank you. The data step executes successfully on both the Windows and Unix side.

Super Contributor
Posts: 644

Re: Invalid data, datalines on SAS BI server

The data read by SAS for adjustment is '2E0D'x : the first character is a period , the second is a carriage return (unusual in this type of input).  You may have a data problem; I'm guessing your server is Unix and is expecting '0A'x for an end of line.

Why have you got carriage returns in your data anyway?  Unnecessary on this input.

Richard

☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 506 views
  • 4 likes
  • 3 in conversation