topic Re: proc import failing, maybe because of line breaks in SAS Programming

proc import failing, maybe because of line breaks

sasprogramming — Wed, 21 Jul 2021 07:13:21 GMT

I am trying to load in a fairly large dataset in which there are line breaks in some rows. This data is fine to view in the csv and can be loaded into R very easily.

How can I get the data loaded in properly?

Code:

proc import
datafile="C:/Users/bbgeoa/Documents/My SAS Files/ALLjune20-may21.csv" 
out= tm.procimport dbms = csv replace;
getnames=yes;
run;

Re: proc import failing, maybe because of line breaks

ChrisNZ — Fri, 02 Jul 2021 04:52:26 GMT

The input statement does not match the data, try adding statement

guessingrows=max;

to help SAS better parse the lengths.

Re: proc import failing, maybe because of line breaks

Ksharp — Fri, 02 Jul 2021 14:08:24 GMT

And make sure "ALLjune20-may21.csv" is created under Windows, if your sas is running under Windows .
Otherwise, you need change end-of-line character like :

filename x 'c:\temp\ALLjune20-may21.csv' termstr=lf;
proc import datafile=x ............

OR

filename x 'c:\temp\ALLjune20-may21.csv' termstr=crlf;
proc import datafile=x ............

Re: proc import failing, maybe because of line breaks

sasprogramming — Sun, 04 Jul 2021 05:09:28 GMT

Doesn't fix the problem, not sure why SAS struggles so hard in reading in data when compared to R/Python

Re: proc import failing, maybe because of line breaks

Patrick — Sun, 04 Jul 2021 07:01:40 GMT

@sasprogramming

This looks like real unmasked data. I STRONGLY recommend that you remove anything PII from this post asap.

Re: proc import failing, maybe because of line breaks

sasprogramming — Sun, 04 Jul 2021 09:08:58 GMT

It was sample data

Re: proc import failing, maybe because of line breaks

Kurt_Bremser — Sun, 04 Jul 2021 09:20:37 GMT

You may be lucky if the unwanted line breaks in text are just CR or LF, while the "real" line breaks are CRLF (or the other way round); in this case, the TERMSTR= option will bail you out.
It is along on-going issue with SAS that line breaks in variables are not recognized as "invalid".
Even a well-supported Ballot Idea has been rejected by SAS, which is considered a severe insult by many of the senior members here.

Re: proc import failing, maybe because of line breaks

Patrick — Sun, 04 Jul 2021 09:31:26 GMT

@sasprogramming That's very comforting to know given that there was a real company name and account numbers in it.

Suggest you follow the advice already given:
- guessingrows=max;

This so SAS first scans the whole source file and then creates variable types and lengths suitable for all of the data.

- TERMSTR=CRLF

I normally use Notepad++ to investigate a text file and select view/show symbols/show all characters - this will tell you what you're dealing with.

- options validvarname=v7;

This so the SAS variable names created comply with SAS naming standards. Any non-compliant character like a blank will get replaced with an underscore.

Re: proc import failing, maybe because of line breaks

Kurt_Bremser — Sun, 04 Jul 2021 09:46:46 GMT

PS I was talking about this Ballot entry:

https://communities.sas.com/t5/SASware-Ballot-Ideas/Enhancements-to-INFILE-FILE-to-handle-delimited-file-variations/idi-p/435977

Re: proc import failing, maybe because of line breaks

Ksharp — Mon, 05 Jul 2021 07:54:44 GMT

Can you post some data of "ALLjune20-may21.csv" ?
So we can test it and find out where is the problem .