How to import a csv file changing the default delimiter.

[Abelp9]

Hello everyone, I'm new to sas programming and I would like to import 4 csv files that have this delimiter: || and I would like to change it to this |""|.

how could i do it? I have tried with this:

proc import file="xxxxxxx\xxxx.csv" 
out=work.test
dbms=dlm
replace;
delimiter='|""|';
run;

but it gives to me this errors:

ERROR: Some code points have not been transcoded.
ERROR: Some code points have not been transcoded.
ERROR: Some code points have not been transcoded.
ERROR: Invalid open mode.
ERROR: Import unsuccessful. See SAS Log for details.

If someone could help me I would be very grateful

[Tom]

A delimiter is a single character.  You can use multiple characters as delimiters, but that just means that each individual character in the delimiter list string is treated as delimiter.  You appear to be trying to say that your file is using a four character string as the delimiter.  Two vertical bar characters around two double quote characters.  If that is really how the file is built you will need to use the DLMSTR= option on the INFILE statement and not the DLM= option.

But your error messages are about the ENCODING of the file and not how the fields are delimited. 

Check the encoding option for your SAS session by checking the ENCODING system option.  You can try either of these two methods to check:

proc options option=encoding; run;

%put %sysfunc(getoption(encoding));

To make sure you can handle any encoding that the file might be using you should be running your SAS session with ENCODING=UTF-8.  If you are using some single byte encoding, like WLATIN1, then there are only 256 possible characters that encoding can reprsesent.

To override the encoding that SAS will use to read the file use the ENCODING= option on the INFILE statement.

To check what your file really has in it look at it as a binary file and dump some of it to the SAS log.  This data step will dump the first 500 bytes from the file to the SAS log so you can check what it actually contains.

data _null_;
   infile "xxxxxxx\xxxx.csv"  recfm=f lrecl=100 obs=5 ;
   input;
   list;
run;

[Abelp9]

Would something like this be? It keeps giving me errors everywhere so I guess not, but what is the function of DLMSTR = ? SAS does not recognize it and I do not know if I am putting it correctly in the encoding

proc import file="xxxxxx\xxxx.csv"
ENCODING= UTF-8
out=work.test
dbms=dlm
replace;
DLMSTR ='|""|';
run;

[Tom · Accepted solution]

Read the file yourself.  Don't ask PROC IMPROT to guess how to read the file.

Try this to see what the first few lines look like:

data _null_ ;
  infile "xxxxxx\xxxx.csv" obs=5;
  input;
  list;
run;

if it really looks like it is using the multiple byte string between the fields then try this to read in some of the values and look at them.

This will read the first 10 fields from the first 10 lines as character strings (truncated to 50 bytes each) and then print it so you can examine what the fields look like.

data test ;
  infile "xxxxxx\xxxx.csv" truncover obs=10 DLMSTR ='|""|';
  input (v1-v10) (:$50.);
run;
proc print;
run;

[Tom]

If you did want to try using PROC IMPORT perhaps you should first convert the file to a normal CSV file instead.

data _null_;
  infile original dlmstr='|""|' length=ll column=cc truncover ;
  file new dsd ;
  do until(cc>ll);
    input value :$32767. @;
    put value @;
  end;
  put;
run;