How to import a csv in SAS to change the delimiter and export it again with the new delimiter

[Tom · Accepted solution]

So the DO loop is not detecting the last empty value.

There are ways to fix that. You could just hard code the number of fields into the DO loop instead of trying to have it detect the number of fields.  If you don't know in advance how many fields there are try checking if the last character on the line is the delimiter and add one more field when it is.  Or try reading the whole file to get a count of the maximum number of fields found.

data _null_;
  infile original dlm='|' dsd truncover ;
  file new dlm='|' ;
  do col=1 to 8 until(cc>ll);
    input value ~ :$char32767. @;
    if cc<ll then input nextch $char1. +(-1) @;
    if missing(value) then value ='""';
    put value :$char32767. @;
  end;
  put;
run;

Results:

823   data _null_;
824     infile new;
825     input;
826     n=countc(_infile_,'|');
827     put n= _infile_;
828   run;

NOTE: The infile NEW is:
      Filename=...\#LN00107,
      RECFM=V,LRECL=32767,File Size (bytes)=56,
      Last Modified=09Feb2022:08:41:17,
      Create Time=09Feb2022:08:41:17

n=7 "Hello"|"what"|""|"hello"|""|""|""|""
n=7 1|2|3|4|5|6|7|8
NOTE: 2 records were read from the infile NEW.
      The minimum record length was 15.
      The maximum record length was 37.

Another method you could use when the lines (after inserting the extra quotes) are shorter than 32,767 bytes is to replicate your manual process.

data _null_;
  infile original truncover ;
  file new dlm='|' ;
  input line $char32767.;
  line=tranwrd(line,'||','|""|');
  length = lengthn(line);
  put line $varying32767. length; 
run;

Results:

840   data _null_;
841     infile new;
842     input;
843     n=countc(_infile_,'|');
844     put n= _infile_;
845   run;

NOTE: The infile NEW is:
      Filename=...\#LN00107,
      RECFM=V,LRECL=32767,File Size (bytes)=52,
      Last Modified=09Feb2022:08:41:17,
      Create Time=09Feb2022:08:41:17

n=7 "Hello"|"what"|""|"hello"|""||""|
n=7 1|2|3|4|5|6|7|8
NOTE: 2 records were read from the infile NEW.
      The minimum record length was 15.
      The maximum record length was 33.

Notice how there is a slight difference.

"Hello"|"what"|""|"hello"|""||""|
"Hello"|"what"|""|"hello"|""|""|""|""

You can adjust to repeat the replacing until all of the || have had "" inserted.

data _null_;
  infile original truncover ;
  file new dlm='|' ;
  input line $char32767.;
  do while (index(line,'||'));
    line=tranwrd(line,'||','|""|');
  end;
  length = lengthn(line);
  put line $varying32767. length; 
run;

Results:

874   data _null_;
875     infile new;
876     input;
877     n=countc(_infile_,'|');
878     put n= _infile_;
879   run;

NOTE: The infile NEW is:
      Filename=...\#LN00107,
      RECFM=V,LRECL=32767,File Size (bytes)=54,
      Last Modified=09Feb2022:08:56:14,
      Create Time=09Feb2022:08:41:17

n=7 "Hello"|"what"|""|"hello"|""|""|""|
n=7 1|2|3|4|5|6|7|8
NOTE: 2 records were read from the infile NEW.
      The minimum record length was 15.
      The maximum record length was 35.

Here is your photograph:

You still have not explained why a NORMAL delimited file without all of those extra quotes is not sufficient.  What are you going to DO with the new file?  What happens when you use a normal CSV file?

You should also try other types of lines.

What about when the last field is not empty?
What about when one or more of the fields is numeric instead of character?  Do you still need the quotes then? Or do they cause trouble?  An example of where they can cause trouble is if you are using PROC IMPORT to read the file.  SAS will (mistakenly) convert any field that has quotes around it on every line into a character string.  Even if it just has quotes because the way the numbers are displayed they include commas. So adding unneeded quotes can have negative side effects.

PS: Please learn how to use the forum editor.  Use the Insert Code icon to insert text.  Use the Insert SAS Code icon to insert SAS code.  Use the Insert Photos icon to insert photographs.

[Abelp9]

Thank you for your help, your God

[Kurt_Bremser]

You are also moving the goalposts here, in your first post the data had no leading pipe. Please clarify this.