topic Re: Embedded Double Quote Delimiter Import PIPE in SAS Programming

Embedded Double Quote Delimiter Import PIPE

shl007 — Tue, 07 Apr 2020 17:33:17 GMT

Hi - I have a CSV file that looks like this:

"Joe the "Best" Company", "45.90"

I'm importing this CSV via a PIPE command and a data step. What's happening is, the name in the first column is getting split due to the embedded double quote delimiter. Is there any way around this? I've included the DSD setting in my import data step using a pipe.

Thanks for any tips.

Re: Embedded Double Quote Delimiter Import PIPE

Reeza — Tue, 07 Apr 2020 17:36:57 GMT

What does the rest of the file look like? If you cannot have an embedded comma for example you can try removing the DSD option and just assume it's comma delimited. You'll need to expand a bit on your issue. If you could include a few lines and the code you're currently using that would be helpful.

Re: Embedded Double Quote Delimiter Import PIPE

Tom — Tue, 07 Apr 2020 17:50:07 GMT

Who generated that file? They should be instructed to generate files that can be parsed.

Re: Embedded Double Quote Delimiter Import PIPE

shl007 — Tue, 07 Apr 2020 21:31:45 GMT

I'm invoking my pipe this way:

filename test pipe "ls -a /mypath/myfile.csv";

Then in a data step:

INFILE test TRUNCOVER ;
INPUT name $100.;
INFILE dummy FILEVAR=name DSD dlm=',' END=last FIRSTOBS=2 LENGTH=len ;

When I remove the DSD from the second infile statement, I get a bunch of "Invalid data" errors ... Is the only solution to fix the input file?

Re: Embedded Double Quote Delimiter Import PIPE

Tom — Tue, 07 Apr 2020 21:56:32 GMT

Your description seems strange. You started off saying you had a file you were reading with PIPE that had this content.

"Joe the "Best" Company", "45.90"

Then you show a fileref using a PIPE that is NOT generating anything at all like that content.

filename test pipe "ls -a /mypath/myfile.csv";

That LS command will generate at the most one line of text. Which is just the text your already gave it. Not sure why it is part of the discussion.

Are you saying the myfile.csv is the file that has the improperly quoted values?

Who generated that file? What software did they use?

It shouldn't really matter how complex your INFILE statement is, the question is how is the INPUT statement working that is trying to read that misquoted line? As long as your values don't also have embedded commas (like the embedded quotes that are causing you trouble) then you should be able to read the line without the DSD option. But the quotes will cause issues.

data test;
  infile cards dlm=',' truncover ;
  length var1 $100 var2 $20 ;
  input var1 var2 ;
  put (var1 var2) (=);
  if var1=:'"' then var1=substrn(var1,2,length(var1)-2);
  if var2=:'"' then var2=substrn(var2,2,length(var2)-2);
  put (var1 var2) (=);
cards;
"Joe the "Best" Company", "45.90"
;

var1="Joe the "Best" Company" var2="45.90"
var1=Joe the "Best" Company var2=45.90

Re: Embedded Double Quote Delimiter Import PIPE

ChrisNZ — Wed, 08 Apr 2020 00:13:08 GMT

Supposing the data is as described, and not coming from ls, this is slightly simpler:

data test;
  infile "&filename" dlm=',' truncover pad ;
  length VAR1 $100 VAR2 $20 ;
  input VAR1 VAR2 ;
  VAR1 = dequote (VAR1);
  VAR2 = dequote (VAR2);
run;