Advanced Search

Remove carriage return and linefeed characters within quoted strings

/ Published in: SAS
Save to your folder(s)
If a flat file contains embedded carriage return (CR) and linefeed characters (LF) inside double quotes, SAS will interpret them as end of line markers. This may cause your file to be read incorrectly.
This example replaces CR/LF characters within double quotes with other printable characters. CR/LF characters outside of double quotes are untouched.

In this sample, the external file is updated in place. You cannot separate the input and output because the code uses shared buffers.

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

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. /* Create sample .csv file containing CR/LF bytes.  '0D'x is the */
  2. /* hexadecimal representation of CR and '0A'x is the hexadecimal */
  3. /* representation of LF.                                         */
  4.  
  5. data _null_;
  6.   file "c:\sample.csv";  
  7.   /* Code the PUT statement so the hex values will be inside the */
  8.   /* the double quotes in the output file SAMPLE.CSV.            */
  9.   put '"field1","field2","field3'
  10.     '0D'x    
  11.     '",'
  12.     '"field4","field5","field6'
  13.     '0A'x   
  14.     '",'
  15.     '"field7","field8","field9"'
  16.   ;
  17. run;
  18.  
  19. /* Read in the test file created above. */
  20. data out;
  21.   /* The DSD option assumes the delimiter is a comma. You can override */
  22.   /* the delimiter with the DLM= option if needed.                     */
  23.   infile "c:\sample.csv" dsd truncover;
  24.   length var1 - var9 $15;
  25.   input var1 - var9 $;
  26. run;
  27.  
  28. proc print;
  29.   title 'File is read incorrectly due to embedded CR/LF';
  30. run;
  31.  
  32. /************************** CAUTION ***************************/
  33. /*                                                            */
  34. /* This program UPDATES IN PLACE, create a backup copy before */
  35. /* running.                                                   */
  36. /*                                                            */
  37. /************************** CAUTION ***************************/
  38.  
  39. /* Replace carriage return and linefeed characters inside     */
  40. /* double quotes with a specified character.  This sample     */
  41. /* uses '@' and '$', but any character can be used, including */
  42. /* spaces.  CR/LFs not in double quotes will not be replaced. */
  43.  
  44.  
  45. %let repA='@';                    /* replacement character LF */
  46. %let repD='$';                    /* replacement character CR */
  47.  
  48.  
  49. %let dsnnme="c:\sample.csv";      /* use full path of CSV file */
  50.  
  51. data _null_;
  52.   /* RECFM=N reads the file in binary format. The file consists    */
  53.   /* of a stream of bytes with no record boundaries.  SHAREBUFFERS */
  54.   /* specifies that the FILE statement and the INFILE statement    */
  55.   /* share the same buffer.                                        */
  56.   infile &dsnnme recfm=n sharebuffers;
  57.   file &dsnnme recfm=n;
  58.  
  59.   /* OPEN is a flag variable used to determine if the CR/LF is within */
  60.   /* double quotes or not.  Retain this value.                        */
  61.   retain open 0;
  62.  
  63.   input a $char1.;
  64.   /* If the character is a double quote, set OPEN to its opposite value. */
  65.   if a = '"' then open = ^(open);
  66.  
  67.   /* If the CR or LF is after an open double quote, replace the byte with */
  68.   /* the appropriate value.                                               */
  69.   if open then do;
  70.     if a = '0D'x then put &repD;
  71.     else if a = '0A'x then put &repA;
  72.   end;
  73. run;
  74.  
  75. /* Read in new version of file to check for success. */
  76. data outp;
  77.   infile "c:\sample.csv" dsd dlm=',' truncover;
  78.   length var1 - var9 $ 15;
  79.   input var1 - var9 $ ;
  80. run;
  81.  
  82. proc print;
  83.   title1 'File is read correctly after transformation ';
  84.   title2 "Look for printable characters &repa &repd in variable values ";
  85. run;
  86.  
  87. /* OPTIONAL -- Delete external file */
  88. data _null_;
  89.   fname="tempfile";
  90.   rc=filename(fname,"c:\sample.csv");
  91.   if rc = 0 and fexist(fname) then rc=fdelete(fname);
  92.   rc=filename(fname);
  93. run;

URL: http://support.sas.com/kb/26/065.html

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.