How to import csv file with delimiter

Reply
Occasional Contributor
Posts: 13

How to import csv file with delimiter

Hi,

I need to import a csv file into SAS. However, the file contains delimiter (which is ',' in my case) as data values also.

e.g., "tablet reagent for bilirubin, glucose, hemoglobin, ketones". This string contains delimiter, when I import SAS creates different columns for values separated with ',', which I do not want. SAS should create a column with value "tablet reagent for bilirubin, glucose, hemoglobin, ketones".

Thanks,

Abheenava

Super User
Super User
Posts: 6,347

Re: How to import csv file with delimiter

This is not a problem that has a generic solution.  You might be able to fix it for a specific file if you can be certain that the extra commas only occur on one variable.

It is best to start with a valid CSV file.  Values that contain the delimiter should be quoted.

Occasional Contributor
Posts: 13

Re: How to import csv file with delimiter

Hi Tom,

I am using below code as per your earlier post:

_infile_=compress(_infile_,"'");

but, still I am not able to get the correct value.

Thanks,

Abheenava

SAS Employee
Posts: 340

Re: How to import csv file with delimiter

"The  DSD option also enables list input to read a character value that contains a delimiter within a quoted string."

Occasional Contributor
Posts: 13

Re: How to import csv file with delimiter

I have tried DSD also. It is not working.

SAS Employee
Posts: 340

Re: How to import csv file with delimiter

Can you post your code and a couple of lines of input text?

Super User
Super User
Posts: 6,347

Re: How to import csv file with delimiter

Eliminating the quotes will just make the problem worse.  If the values with commas are quoted then the DSD option on INFILE will automatically take care of reading it.  Otherwise you are going to have resort to some type of comma or field counting.

Esteemed Advisor
Esteemed Advisor
Posts: 7,217

Re: How to import csv file with delimiter

I would completely agree with Tom, you are far better off choosing a delimiter which does not appear in the data (or setting the file up with more identification - XML for example).  However, if you are stuck with that file, then you could do something manually yourself:

data want;

  length buffer $2000.;

  infile "s:\temp\rob\text.txt" dlm="¬";

  input buffer $;

  array var{5} $200.;

  i=1;

  do while (scan(buffer,i,'","') ne "");

    var{i}=scan(buffer,i,'"');

    i=i+1;

  end;

run;

Assuming text.txt looks something like:

"tablet reagent for bilirubin, glucose, hemoglobin, ketones","Another var"

The above will split the string read in into var{x}.  Note, this will still leave you with variables which just contain "," so you will need to second pass remove these, and then shift the rest of the array left - if var{x}="," then do; move var{x} to var{x-1};

Its not ideal though and can cause more headaches than its worth.

Ask a Question
Discussion stats
  • 7 replies
  • 384 views
  • 0 likes
  • 4 in conversation