- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
What is the result, missing values?
Anything in the log?
How is the source columns defined - CHAR(11)?
/Linus
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
There may be some characters in fld35.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Agreed! In my haste I didn't even notice that it was using proc sql and I mistook the commas for semicolons.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Actually, just like fld34, there is a 3 digit number in the field, so no characters at all.
Thomas
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
pretty frustrating, none of that helps - I think I'll contact tech support.
Thanks for your help guys!
th
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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