BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
thomash123
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
thomash123
Calcite | Level 5

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

13 REPLIES 13
LinusH
Tourmaline | Level 20

What is the result, missing values?

Anything in the log?

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

/Linus

Data never sleeps
thomash123
Calcite | Level 5

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

Linlin
Lapis Lazuli | Level 10

There may be some characters in fld35.

art297
Opal | Level 21

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.

Doc_Duke
Rhodochrosite | Level 12

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.

art297
Opal | Level 21

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

thomash123
Calcite | Level 5

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

Thomas

art297
Opal | Level 21

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.

Ksharp
Super User

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

thomash123
Calcite | Level 5

Hi,

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

Thanks for your help guys!

th

thomash123
Calcite | Level 5

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

Tom
Super User Tom
Super User

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.

thomash123
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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