topic Problem converting a value in SAS Programming

Problem converting a value

thomash123 — Wed, 30 Nov 2011 08:41:36 GMT

Dear all,

I am facing a strange issue here which I currently cannot resolve - please help.

A piece of code I've written imports a CSV file into a temporary dataset, which works perfectly fine.

After that, I want to convert some of the values I have imported into numeric data. I am using DI Studio, so I use a "SAS Extract" and the code that gets produced looks like this:

proc sql;

create view work.W5O43ACY as

select

(input(fld1,11.)) as NR length = 8

format = 11.

informat = 11.

label = 'NR',

fld2 as ABLAUFDATUM,

fld3 as ENTITAET,

(input(fld4,11.)) as TEC_ID length = 8

format = 11.

informat = 11.

label = 'TEC_ID',

[...]

(input(fld34,11.)) as TEC_LCR_AK_GP_ID_ALT length = 8

format = 11.

informat = 11.

label = 'TEC_LCR_AK_GP_ID_ALT',

(input(fld35,11.)) as TEC_LCR_AK_GP_ID_NEU length = 8

format = 11.

informat = 11.

label = 'TEC_LCR_AK_GP_ID_NEU'

from &SYSLAST

;

quit;

Now, everything from fld1 to fld34 gets transferred and (if required) converted - only fld35 does not and I have absolutely no clue why. FLD35 does not seem like a reserved syntax.

I am using SAS 9.2 M3.

Thanks,

Thomas

Problem converting a value

LinusH — Wed, 30 Nov 2011 12:45:04 GMT

What is the result, missing values?

Anything in the log?

How is the source columns defined - CHAR(11)?

/Linus

Re: Problem converting a value

thomash123 — Wed, 30 Nov 2011 12:56:55 GMT

UPDATE:

I actually found right at the very top of the log a note

NOTE: Invalid string.

NOTE: Invalid argument to function INPUT. Missing values may be generated.

but I have no clue to what it refers as the job quits successfully and all the other input()-functions work just fine.

Yes, the result is a missing value, the log says nothing of interest in this matter and the source column is char(11) - just like others, that are converted just fine.

Re: Problem converting a value

Linlin — Wed, 30 Nov 2011 13:40:54 GMT

There may be some characters in fld35.

Re: Problem converting a value

art297 — Wed, 30 Nov 2011 13:55:17 GMT

I think you may just be missing a semi-colon at the end of one of your statements. Way toward the end of your code there is a line:

label = 'TEC_LCR_AK_GP_ID_NEU'

add a semi-colon to the end of that line and let us know what happens.

Re: Problem converting a value

Doc_Duke — Wed, 30 Nov 2011 14:19:58 GMT

Art -- the semi-colon is properly placed for a SQL statement. I suspect that Linlin found the answer -- garbage in the field.

Tomash123 -- the garbage may not be printable. Try a PROC PRINT on some of the data applying a HEX format to the field.

Re: Problem converting a value

art297 — Wed, 30 Nov 2011 14:39:26 GMT

Agreed! In my haste I didn't even notice that it was using proc sql and I mistook the commas for semicolons.

Problem converting a value

thomash123 — Wed, 30 Nov 2011 15:08:23 GMT

Actually, just like fld34, there is a 3 digit number in the field, so no characters at all.

Thomas

Problem converting a value

art297 — Wed, 30 Nov 2011 15:21:02 GMT

Will it work if you imbed a compress function in the input statement for field35? I can't test it at the moment, but am wondering if something like:

(input(compress(fld35,,'kd'),11.)) as TEC_LCR_AK_GP_ID_NEU length = 8

might get rid of whatever is causing the error.

Problem converting a value

Ksharp — Thu, 01 Dec 2011 07:34:23 GMT

If the data only contain digits.

Maybe length of variable fld35 is too wide. input can't transform it correctly.

input(left(fld35),11.)

input fld35, 32.)

Ksharp

Problem converting a value

thomash123 — Thu, 01 Dec 2011 08:27:32 GMT

Hi,

pretty frustrating, none of that helps - I think I'll contact tech support.

Thanks for your help guys!

Problem converting a value

thomash123 — Mon, 19 Dec 2011 10:37:49 GMT

So, it turns out that there was for some reason a <CRLF> at the end of the CSV line and I had to use termstr=CRLF in the INFILE statement.

Pretty interesting though, that DI studio shows a fully imported dataset but seems not to be able to convert the value - and how can it, if the value is not there?

So I would expect the tool to give me some hint that a value is actually missing instead of showing it in the temp data set.

Thomas

Problem converting a value

Tom — Mon, 19 Dec 2011 13:42:38 GMT

The usual reason for this is that you have a text file created on a PC that has been moved as binary to a UNIX system.

DOS (windows) uses CR+LF pair to mark the end of a line. UNIX just uses a LF. So when you read the file on UNIX the extra CR character becomes the last character on the line. The new TERMSTR option on the INFILE statement will tell SAS that the CR is part of the end of line marker and it will be stripped from the input buffer before the SAS statements read the line.

Problem converting a value

thomash123 — Mon, 19 Dec 2011 16:34:35 GMT

Thanks, Tom - I just did not find out about it, because on the one hand this never happend before when transferring the files and on the other hand the behaviour in DI studio is so odd and actually shows me a correctly imported file.