DATA Step, Macro, Functions and more

Problem converting a value

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

Problem converting a value

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


Accepted Solutions
Solution
‎12-19-2011 05:37 AM
Contributor
Posts: 38

Problem converting a value

Posted in reply to thomash123

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

View solution in original post


All Replies
Super User
Posts: 5,426

Problem converting a value

Posted in reply to thomash123

What is the result, missing values?

Anything in the log?

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

/Linus

Data never sleeps
Contributor
Posts: 38

Re: Problem converting a value

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.

th

Super Contributor
Posts: 1,636

Re: Problem converting a value

Posted in reply to thomash123

There may be some characters in fld35.

PROC Star
Posts: 7,468

Re: Problem converting a value

Posted in reply to thomash123

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.

Trusted Advisor
Posts: 2,115

Re: Problem converting a value

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.

PROC Star
Posts: 7,468

Re: Problem converting a value

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

Contributor
Posts: 38

Problem converting a value

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

Thomas

PROC Star
Posts: 7,468

Problem converting a value

Posted in reply to thomash123

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.

Super User
Posts: 10,023

Problem converting a value

Posted in reply to thomash123

If the data only contain digits.

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

input(left(fld35),11.)

OR

input fld35, 32.)

Ksharp

Contributor
Posts: 38

Problem converting a value

Hi,

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

Thanks for your help guys!

th

Solution
‎12-19-2011 05:37 AM
Contributor
Posts: 38

Problem converting a value

Posted in reply to thomash123

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

Super User
Super User
Posts: 7,039

Problem converting a value

Posted in reply to thomash123

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.

Contributor
Posts: 38

Problem converting a value

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.

th

🔒 This topic is solved and locked.

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

Discussion stats
  • 13 replies
  • 2090 views
  • 0 likes
  • 7 in conversation