topic Re: Input a CSV file with text fields that contain line breaks + carriage returns and ',' signs in SAS Programming

Input a CSV file with text fields that contain line breaks + carriage returns and ',' signs

simon_98 — Tue, 05 Jul 2022 14:03:01 GMT

Hello, I want to import a CSV file using the infile and input procedure. The problem is that the column 'review' is a text surrounded by quotas like ".........." and it contains carriage returns + line breaks and contains comma (',') in the text. Is the a way to import this type of csv file?

Example of the data:

n,recommendationid,language,review

0,77060058,english,"Really great game!

The story is the most ambitious and inventive campaign ive ever played, it's a must play for anyone who loves Guns, Horses, cowboys, or video games. The Story is emotional and rich and is worth every second. The online portion of this game has gotten better since the original release in 2018;

However, there is still a lot to be desired. Rockstar is slowly updating it, but this game is worth it for the story mode alone.",1601870541,1601870541,True,0,0,0,0,True,False,False,76561198262327743,33,9,2737,2737,2737,1601868447

1,77059990,english,"Red Dead Redemption 2. Is there anything I can say that will suffice in describing this masterpiece??? RDR2 is SUCH a good game. It’s definitely my favorite game. Its price is high for good reason. The storage space it takes is worth it. RDR2 is a game that has such an immersive story line. It’s a long, but **bleep** near perfect game. This is the first game that has made me bawl like a baby. If you get this game, or if you’ve already played it, you’ll understand why I cried... That said, I highly recommend this game. Again, **bleep** near perfect.",1601870400,1601870400,True,0,0,0,0,True,False,False,76561198303413791,48,7,5029,0,5029,1597432775

2,77058854,english,"how to eat monkey,

ye s

",1601868148,1601868148,True,0,0,0,0,True,False,False,76561198425061844,6,2,442,442,412,1601869925

Re: Input a CSV file with text fields that contain line breaks + carriage returns and ',' signs

Ksharp — Wed, 06 Jul 2022 12:25:37 GMT

Can you post some real data? It is depend on the pattern that start a new line .

data temp;
infile 'c:\temp\have.csv' firstobs=2 length=len ;
input have $varying8000. len;
if missing(have) then delete;
if prxmatch('/^\d{1,},\d{4}/',have) then group+1;
run;
data temp2;
 do until(last.group);
  set temp;
  by group;
  length row $ 30000;
  row=cats(row,have);
 end;
keep group row;
run;
data want;
 set temp2;
n=scan(row,1,',','mq');
recommendationid=scan(row,2,',','mq');
language=scan(row,3,',','mq');
review=scan(row,4,',','mq');
run;

Re: Input a CSV file with text fields that contain line breaks + carriage returns and ',' signs

SASJedi — Wed, 06 Jul 2022 13:02:19 GMT

Another approach (which will get this in a single pass):

filename readme "c:\temp\have.txt";
data recommendataions;
   length n recommendationid 8 language $20 Recommend $1000;
   drop _: r;
   infile readme dsd _infile_=raw truncover;
   input n recommendationid language:$20. R:$1000.;
   if countc(raw,'"')=2 then do;
      Recommend=r;
      output;
   end;
   else do;
      _first=not (missing(n) and missing(language)); 
      if _first then do until (_n>0);
         input ;
         _n=find(raw,'"');
         if _n=0 then do;
            R=catx(' ',r,raw);
         end;
         else do;
            R=catx(' ',R,substr(raw,1,_n-1));
            if find(r,'"') then Recommend=scan(r,1,'"');
            else Recommend=R;
            output;
            leave;
         end;
      end;
   end;
run;

Result:

Obs	n	recommendationid	language	Recommend
1	0	77060058	english	Really great game! The story is the most ambitious and inventive campaign ive ever played, it's a must play for anyone who loves Guns, Horses, cowboys, or video games. The Story is emotional and rich and is worth every second. The online portion of this game has gotten better since the original release in 2018; However, there is still a lot to be desired. Rockstar is slowly updating it, but this game is worth it for the story mode alone.
2	1	77059990	english	Red Dead Redemption 2. Is there anything I can say that will suffice in describing this masterpiece??? RDR2 is SUCH a good game. It’s definitely my favorite game. Its price is high for good reason. The storage space it takes is worth it. RDR2 is a game that has such an immersive story line. It’s a long, but bleep near perfect game. This is the first game that has made me bawl like a baby. If you get this game, or if you’ve already played it, you’ll understand why I cried... That said, I highly recommend this game. Again, bleep near perfect.
3	2	77058854	english	how to eat monkey ye s

Re: Input a CSV file with text fields that contain line breaks + carriage returns and ',' signs

Tom — Wed, 06 Jul 2022 13:17:37 GMT

It depends on what exact characters and character combinations are in the quoted strings.

Comma and carriage control are characters, but "line break" is not a character, it is a concept. SAS expects end of line characters to indicate the end of each line. The INFILE statement lets you use the TERMSTR= option choose between three different possible end of line character strings. It could be a single linefeed character, '0A'x, a single carriage return character, '0D'x, or a carriage return + line feed combination.

So if the characters that your file is using as the end of line marge is CR+LF and the characters embedded in the strings are only single LF or single CR then just adding TERMSTR=CRLF to your INFILE statement will let you read the file as it is.

Note it is hard to tell exactly what you have from text pasted into this forum as the forum editor could be modifying it for its purposes.

To see what is in the file look at the actual character codes. The easiest way is to tell SAS to read it as fixed length blocks and dump it to the log using the LIST statement. So to read the first 500 bytes of the file you could use something like this.

data _null_;
  infile 'myfile.csv' lrecl=100 recfm=f obs=100;
  input;
  list;
run;

If you see that the actual end of line characters and the "line breaks" in the middle of the string are using the same actual characters then you will probably want to pre-process the file to fix that.

You could try this %replace_crlf() macro

Re: Input a CSV file with text fields that contain line breaks + carriage returns and ',' signs

simon_98 — Wed, 06 Jul 2022 14:05:27 GMT

Of course, I am sorry to not have included it in the first place.

I attached the original csv file.

Re: Input a CSV file with text fields that contain line breaks + carriage returns and ',' signs

simon_98 — Wed, 06 Jul 2022 14:14:06 GMT

When I try to use TERMSTR=CRLF, it does not give me any output at all.

Attached would be the original csv file.

Re: Input a CSV file with text fields that contain line breaks + carriage returns and ',' signs

Tom — Wed, 06 Jul 2022 14:51:28 GMT

That is because the end of line markers in that file are just linefeed, which is the default for Unix files. Also that file does NOT use semicolon as the delimiters, just normal old commas.

So fix the embedded LFs and PROC IMPORT can do a reasonable jobs of guessing what is in the file.

filename old "c:\downloads\1174180_RedDeadRedemption2_edited2.csv" ;
filename new "c:\downloads\fixed.txt";

%replace_crlf(old,new);

proc import datafile=new out=want replace dbms=csv ;
run;

proc means;
run;

But I suspect that AUTHOR_STEAMID should NOT be defined a NUMERIC. You normally do not need to do arithmetic with ID values. AUTHOR_LAST_PLAYED also does not look like it should be numeric, but its values are not so large that they will cause trouble if stored as a number.

So just write your own data step to read the file. You can write something that is MUCH clearer and easier to understand than the stuff that PROC IMPORT emits.

data WORK.WANT    ;
  infile NEW DSD TRUNCOVER firstobs=2 ;
  length
     n 8
     recommendationid 8
     language $7
     review $1248
     timestamp_created 8
     timestamp_updated 8
     voted_up $5
     votes_up 8
     votes_funny 8
     weighted_vote_score 8
     comment_count 8
     steam_purchase $4
     received_for_free $5
     written_during_early_access $5
     author_steamid $20
     author_num_games_owned 8
     author_num_reviews 8
     author_playtime_forever 8
     author_playtime_last_two_weeks 8
     author_playtime_at_review 8
     author_last_played 8
  ;
  format timestamp_created timestamp_updated author_last_played datetime19.
         author_playtime_forever author_playtime_last_two_weeks author_playtime_at_review time12.
  ;
  input n -- author_last_played;
run;

proc summary;
  var _numeric_;
  output out=summary  ;
run;

proc print data=summary;
 where _stat_ =: 'M';
 id _stat_;
run;

Results

                                                                                                                votes_    weighted_
_STAT_   _TYPE_   _FREQ_      n      recommendationid     timestamp_created     timestamp_updated   votes_up     funny   vote_score

 MIN        0      11580    0.0000      59294641.00      04DEC2009:18:07:58    23MAY2010:15:42:05       0.00     0.000     0.00000
 MAX        0      11580   99.0000      77060058.00      05OCT2010:04:02:21    05OCT2010:04:02:21    3012.00   339.000     0.96853
 MEAN       0      11580   49.4309      72591600.59      15JUL2010:23:53:39    28JUL2010:00:32:56       2.08     0.353     0.17544

                       author_                         author_                             author_
         comment_    num_games_    author_num_       playtime_    author_playtime_       playtime_
_STAT_     count        owned        reviews           forever     last_two_weeks        at_review     author_last_played

 MIN       0.0000         0.00          1.00           0:00:05           0:00:00           0:00:05     05DEC2009:06:06:35
 MAX      68.0000      9708.00       1886.00          91:16:14           5:29:45          89:16:07     07OCT2010:05:59:45
 MEAN      0.0959       122.64         10.08           2:01:41           0:06:58           1:10:39     29AUG2010:03:06:36

Re: Input a CSV file with text fields that contain line breaks + carriage returns and ',' signs

simon_98 — Wed, 06 Jul 2022 20:11:49 GMT

Thank You so much, it helped me a lot and it is working now!!

Re: Input a CSV file with text fields that contain line breaks + carriage returns and ',' signs

Ksharp — Thu, 07 Jul 2022 11:37:48 GMT

@Tom

Why in your result , there are some '\n' chatacters but in CSV don't have ?

Re: Input a CSV file with text fields that contain line breaks + carriage returns and ',' signs

Tom — Thu, 07 Jul 2022 13:23:32 GMT

That is the default replacement character that the %replace_crfl() macro used for the embedded linefeed characters.

You can specify something else when you call the macro. Read the source code for the macro to get more help.

%macro replace_crlf
/*----------------------------------------------------------------------------
Replace carriage return or linefeed characters that are inside quotes
----------------------------------------------------------------------------*/
(infile   /* Fileref or quoted physical name of input file */
,outfile  /* Fileref or quoted physical name of output file */
,cr='\r'  /* Replacement string for carriage return */
,lf='\n'  /* Replacement string for linefeed */
);

Re: Input a CSV file with text fields that contain line breaks + carriage returns and ',' signs

jimbobob — Mon, 12 Sep 2022 20:46:48 GMT

@Tom is there a way to run that macro on multiple files, I have this issue with lots of files we get everyday, is there a way to use this macro to loop thru all the files in the directory and apply the cleanup macro.

Re: Input a CSV file with text fields that contain line breaks + carriage returns and ',' signs

Tom — Mon, 12 Sep 2022 23:05:13 GMT

@jimbobob wrote:

@Tom is there a way to run that macro on multiple files, I have this issue with lots of files we get everyday, is there a way to use this macro to loop thru all the files in the directory and apply the cleanup macro.

The easiest way is to make a dataset with the names of the files and use that to generate a separate macro call for each file.

There are plenty of postings on the site (and even some examples in the SAS manuals, see DREAD() function) for how to make the list.

data _null_;
  set list ;
  call execute(cats('%nrstr(replace_crlf)(',quote(trim(filename)),',',quote(cats(filename,'.fixed')),')'));
run;

Re: Input a CSV file with text fields that contain line breaks + carriage returns and ',' signs

Ksharp — Tue, 13 Sep 2022 12:14:46 GMT

@Tom ,

It seems that your macro ONLY can handle the '0D0A' in the quote,but what if they are not in quote(like the following picture) ?

I run your macro and get ERROR. but mine don't.

Re: Input a CSV file with text fields that contain line breaks + carriage returns and ',' signs

Ksharp — Tue, 13 Sep 2022 12:18:49 GMT

Ha, @Tom

I run your code by Unicode SAS and get no ERROR,but get wrong result !

Re: Input a CSV file with text fields that contain line breaks + carriage returns and ',' signs

Tom — Tue, 13 Sep 2022 12:51:53 GMT

Yes.

If the line breaks are not in quotes deciding whether or not there are extra line breaks is an impossible task.

You can make it work if you make other assumptions about the file. Things like, the delimiter will never appear as part of the data. The extra line breaks will appear in only specific field(s).

Re: Input a CSV file with text fields that contain line breaks + carriage returns and ',' signs

Tom — Tue, 13 Sep 2022 12:53:05 GMT

Post an minimal reproducible example file and we can look at it.

Re: Input a CSV file with text fields that contain line breaks + carriage returns and ',' signs

Ksharp — Tue, 13 Sep 2022 12:59:23 GMT

Sure. Here is . I modify it at line 7 and 8 as showed in my picture above.

My code could get right result.

Re: Input a CSV file with text fields that contain line breaks + carriage returns and ',' signs

Tom — Tue, 13 Sep 2022 14:42:59 GMT

How is a file with almost 30,000 lines MINIMAL?

NOTE: 29960 records were read from the infile 'c:\downloads\have.csv'.
The minimum record length was 0.
The maximum record length was 6546.

The issue has nothing to do with UTF-8 support.

That file has a linefeed in the middle of the unquoted word english.

Re: Input a CSV file with text fields that contain line breaks + carriage returns and ',' signs

Ksharp — Wed, 14 Sep 2022 11:50:21 GMT

@Tom

“The issue has nothing to do with UTF-8 support.”

I mean SAS LOG didn't show any ERROR info, therefore I am supposed to import correctly. However, I get wrong/screw result. as the following pictures showed:

Re: Input a CSV file with text fields that contain line breaks + carriage returns and ',' signs

Tom — Wed, 14 Sep 2022 11:57:18 GMT

If the CSV file is not validly constructed then any attempt to read it is going to have problems.

The macro you are asking about is designed to remove linefeeds or carriage returns that are inside of quoted strings. The file you provided has (at least) an extra linefeed inside of a string that is not quoted. The word english that you circled in red in one of your photographs.

So if you want to fix that file you will need to use a different algorithm. Or just fix it manually using a text editor.