BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
mattbnorris
Fluorite | Level 6
filename csvnew '\\mydrive\DATA.csv';

DATA mydata.; 
    LENGTH
		VAR1 	$ 4
		VAR2	$ 4
		VAR3	$ 1
		VAR4	$ 3
		VAR5	$ 1
		VAR6	$ 1
		VAR7	$ 6
		VAR8 	$ 6
		VAR9	$ 1;
    FORMAT
		VAR1	$4.
		VAR2	$4.
		VAR3	$1.
		VAR4	$3.
		VAR5	$1.
		VAR6	1.
		VAR7 	6.
		VAR8 	6.
		VAR9	1.;
    INFORMAT
		VAR1	$4.
		VAR2	$4.
		VAR3	$1.
		VAR4	$3.
		VAR5	$1.
		VAR6	1.
		VAR7 	6.
		VAR8 	6.
		VAR9	1.;
    INFILE &csvnew.
        LRECL=67108864
        DLM='2C0D'x
        DSD firstobs=1;
    INPUT
		VAR1	$
		VAR2	$
		VAR3	$
		VAR4	$
		VAR5	$
		VAR6
		VAR7
		VAR8
		VAR9
                @@;
RUN;

Hello, attached is the code I've been using to import a csv that I've been receiving for years now. I thought this was working properly until I noticed some rows were missing. After loading the csv into a data model in excel I've realised there are 2,091,600 rows whereas SAS is only getting 1,950,158 rows. I have opened the CSV in notepad (too big to view in excel) and there is nothing to suggest why SAS would stop at this row - no missing row or strange characters or spaces; it just carries on like the rest of the CSV. There does seem to be something strange going on though because upon running the data step / infile shown, I get a "lost card" note for the record after row 1,950,158 and also it's reading the CSV as 1 line, hence why I think the "@@" is needed in the input section of the data step. Furthermore, I have tried using a simple proc import  but it imports the CSV as one row only with thousands of columns as if it's not recognising that there are multiple rows in the csv.

mattbnorris_0-1682505740223.png

Anyone know how to help?

Thanks,

Matt

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Talk to the creator of the file and ask them what they changed.

If the file looks like ONE line to SAS then it is not using the normal end of line characters.

 

Try making this change:

filename csvnew '\\mydrive\DATA.csv' termstr=cr;

Or just LOOK at the beginning of the file and figure out for yourself what character(s) it uses to mark the end of the lines and what character it uses between the fields.

data _null_;
  infile csvnew lrecl=100 recfm=f obs=5 ;
  input;
  list;
run;

View solution in original post

5 REPLIES 5
Kurt_Bremser
Super User

Open the file with a good programming text editor (like Notepad++). It will let you see the exact nature of column delimiters and line terminators.

Use an appropriate TERMSTR= option for the line terminator

Mac: CR

UNIX/Linux: LF

Windows/DOS: CRLF

mattbnorris
Fluorite | Level 6
Thanks for the help TERMSTR=CR worked!
Tom
Super User Tom
Super User

Talk to the creator of the file and ask them what they changed.

If the file looks like ONE line to SAS then it is not using the normal end of line characters.

 

Try making this change:

filename csvnew '\\mydrive\DATA.csv' termstr=cr;

Or just LOOK at the beginning of the file and figure out for yourself what character(s) it uses to mark the end of the lines and what character it uses between the fields.

data _null_;
  infile csvnew lrecl=100 recfm=f obs=5 ;
  input;
  list;
run;
mattbnorris
Fluorite | Level 6
Thanks for the help TERMSTR=CR
Tom
Super User Tom
Super User

@mattbnorris wrote:
Thanks for the help TERMSTR=CR

If the creator is using Excel on a Mac ask them to be more careful when saving the spreadsheet to a CSV file.  For some reason Excel never adapted correctly to Apple switching to Unix in 2001.  So the first choice if offers for a CSV file writes files with CR as the end of line character.  But if they hunt a little longer in the list of formats they can find one that uses normal end of line characters.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 5 replies
  • 1208 views
  • 1 like
  • 3 in conversation