BookmarkSubscribeRSS Feed
milts
Pyrite | Level 9
Hi All,

I created a fixed-width source table from a csv file. I also imported the column definitions from a csv file.

Then I created a job that uses that source table to create an exact copy of it in a sas data set. But i get errors regarding on invalid data. How come do i get those errors when i didn't specify any constraints?

Also is there a maximum length for fields which come from csv files? I have a column character with length 80 but when I run the job complete value wasn't captured.

Hope you could help me out. Many thanks!
Milton
10 REPLIES 10
milts
Pyrite | Level 9
Correction

Its a TXT source not a CSV source sorry.

The column definitions is the CSV file.

Many thanks
Milton
LinusH
Tourmaline | Level 20
Please attach a log so that you error messages can be available to be commented on.
Your error could be that you are specifying a numeric column, but there exists for some reason alphanumeric data.
For the field length, try using a informat with the lenght of 80.

/Linus
Data never sleeps
milts
Pyrite | Level 9
Hi Linus,

In the log there are error messages saying invalid data for value SOME_DATE and yes it's specified as numeric but there are no alphanumeric characters. But the problem is that there is no value for that field in the text source file. I checked the treat unassigned values to missing in the option also.

Regarding the field length, thanks it's now working.

Regards,
Milton
deleted_user
Not applicable
It sounds like you ERROR is because there is some value in an expected date field that is not a valid date. For example you migh have something like '9999/99/99' in your field, or a year may be outside of the range specified by YEARCUTOFF (From memory, I think the highest year by default is 5499?)

As for your field length problem, I suspect that the issue will lie in the Begin & End position columns in your external table definition. You'll most likely need to check the overlap tick box before you're allowed to change them though. I guess you specified it incorrectly in the first place, and then just changed the Length column in the definition??
milts
Pyrite | Level 9
Hi simkinp,

I have checked the raw text file and the error appears on rows which has no value/blank in that field. that's why it's a little weird.

Regards,
Milton
Patrick
Opal | Level 21
Hi Milton

Try this:
In your "fixed-width source table" object in the tab "File Parameters"
- check the box "Pad column values with blanks"
- check the box "Treat unassigned values as missings"
- Choose a "logical record length" big enough for the longest line of data in your text file.

If this doesn't help:
Please send the log and/or the part of the generated code where the "infile" statement is.

HTH
Patrick


P.S:
The important checkbox is "Treat unassigned values as missings" as this adds a "truncover" to the infile statement.

Checkbox "Pad column values with blanks" adds a "pad" to the infile statement. Actually no more necessary as there is already a "truncover" - but it doesn't hurt to have it as well.
milts
Pyrite | Level 9
Hi Patrick,

Those are checked already. truncover is in the option and the lrecl i set is 32000.

Will there be a difference if I add the DSD option?

I'll post the logs by Monday.

Regards,
Milton
Patrick
Opal | Level 21
"Will there be a difference if I add the DSD option?"
Not with fixed length as you are reading by column position and don't use a delimiter to decide where a new field starts in the record.

If truncover is already there:
Could it be that there are some invisible control characters in your text file. That can happen when textfiles are copied from Windows to Unix with Unix only having LF at the end of a line and Windows having CR/LF.
Is this field you're having problems with at the end of your record?
Peter_C
Rhodochrosite | Level 12
when handling delimited data allways use DSD.
Otherwise it isn't needed.
peterC
deleted_user
Not applicable
Apologies, I mis-read data as date so my first comment is kind of irrelevant! (The regular Friday evening rush to leave work!)

As for your truncation problem, I'm presuming that you can actually see your data is being truncated? DI Studio will not truncate your data unless it is told to do so, and it won't even map you column if the target is shorter than the source, Meaning the problem is certainly in the external file.

I'd suggest you've got one of the following issues

Your LRECL is too short meaning just your last column has been truncated.

or

There is a difference of opinion between your Length, Informat and Begin/End positions (The length of these should all be the same). Don't trust the import wizard, it does get things wrong occaisionally.

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
  • 10 replies
  • 1030 views
  • 0 likes
  • 5 in conversation