topic Re: Invalid data in SAS Programming

Invalid data

balinsk1 — Mon, 11 Jul 2022 02:40:25 GMT

Hello,

I keep getting error messages and have not been able to figure out how to adjust my data to make this work. Any help would be greatly appreciated. Thanks!

Re: Invalid data

Kurt_Bremser — Mon, 11 Jul 2022 05:31:28 GMT

Take the data step code from the log, and change the type of GROC to character.

Or set GUESSINGROWS=MAX for PROC IMPORT.

Re: Invalid data

balinsk1 — Mon, 11 Jul 2022 14:09:11 GMT

I tried both of these things and was still not able to get it to work. Also the GROC variable is meant to be numeric so I don't want it to be read as a character variable. Any other thoughts?

Re: Invalid data

Tom — Mon, 11 Jul 2022 14:25:04 GMT

Why did you go to the trouble of making a PDF file and adding as an attachment instead of just copying the text and pasting it into your question.

For example here are the notes from the end of the data step about how many lines read:

 NOTE: 203 records were read from the infile REFFILE.
 The minimum record length was 50.
 The maximum record length was 321.
 NOTE: The data set WORK.IMPORT5 has 203 observations and 59 variables.

There is no way you could have a line with only 50 characters that contains values for 59 variables.

Most likely some of the lines have embedded end of line character in them and that is confusing the data step.

Remove those end of line characters.

Since the file only has 203 lines (and will have less once you fix it) just open it in a TEXT editor and remove those offending end of lines.

Re: Invalid data

Kurt_Bremser — Mon, 11 Jul 2022 16:42:50 GMT

@balinsk1 wrote:

I tried both of these things and was still not able to get it to work. Also the GROC variable is meant to be numeric so I don't want it to be read as a character variable. Any other thoughts?

Look at your log. "T04" is positively not numeric.

Edit: it seems to be what @Tom already found. You have line breaks in character columns.

Re: Invalid data

balinsk1 — Mon, 11 Jul 2022 18:50:39 GMT

So the T04 is actually input for the therapist ID variable and not for GROC. The therapist ID variable is meant to be a character variable but the GROC variable is meant to be numeric.

Re: Invalid data

balinsk1 — Mon, 11 Jul 2022 18:51:39 GMT

Hi,
I am not sure how to remove embedded end of line characters. What is the best way to do this?

Re: Invalid data

Kurt_Bremser — Mon, 11 Jul 2022 18:56:14 GMT

See here: https://support.sas.com/kb/26/065.html

Re: Invalid data

Tom — Mon, 11 Jul 2022 19:50:25 GMT

@balinsk1 wrote:
Hi,
I am not sure how to remove embedded end of line characters. What is the best way to do this?

You don't know how to edit a text file? How did you create the SAS program code then?

You should have some type text editor on your computer. Such as NOTEPAD program that comes with WIndows. Just open the file in the editor. find the lines where the line breaks are. It should be obvious. In your posted log the second part all started with a quote. Just delete the end of line before the quote on those broken lines.

If you want to use CODE to fix the file here is a link to a macro that will attempt to fix it. But it will only work on files that are corrupted in a well defined pattern. That is they have extra end of line characters enclosed in double quote characters.

https://github.com/sasutils/macros/blob/master/replace_crlf.sas

Re: Invalid data

Tom — Mon, 11 Jul 2022 19:53:31 GMT

@Kurt_Bremser wrote:

See here: https://support.sas.com/kb/26/065.html

If you use that code make sure to run it on a COPY of the file as it modifies the file you run it against.

They even include a warning on the Knowledge Base page.

You should make a copy of your file before running this sample on it.