topic Re: Validate delimiter used in a text file in SAS Programming

Validate delimiter used in a text file

aomy82 — Thu, 24 Dec 2015 16:50:09 GMT

Happy Holidays,

How do I validate what delimiter is used in a text file?

I have a set of text files that need to be pipe delimited. I need a SAS program that checks the text file and confirms the delimiter is a "|".

I can not figure out how to do this.

Re: Validate delimiter used in a text file

LinusH — Thu, 24 Dec 2015 21:01:53 GMT

So what is your validation rule?
You could count the no of pipes in the automatic _infile_ variable and see if matches no of variables-1.

Re: Validate delimiter used in a text file

PGStats — Fri, 25 Dec 2015 05:27:23 GMT

This is how to create a list of all CSV files in a directory and the number of comma separated fields on the first line

data _null_;
length thisFile $200;
infile "&sasforum\datasets\*.csv" eov=firstLine filename=thisFile;
input;
file "&sasforum\datasets\csvList.txt";
if _n_=1 or firstLine then do;
    fields = countw(_infile_, ",");
    put fields z4. +1 thisFile;
    firstLine = 0;
    end;
run;

tested on a Windows system.

Re: Validate delimiter used in a text file

aomy82 — Sat, 26 Dec 2015 17:00:20 GMT

Thanks. I do not currently have access to my work PC but will try out your suggestions and let you know if it works for me.

Re: Validate delimiter used in a text file

aomy82 — Sat, 26 Dec 2015 17:02:26 GMT

Thanks for the example. I do not currently have access to my work PC but will let you know once I have tried it.

Re: Validate delimiter used in a text file

TomKari — Sun, 27 Dec 2015 02:40:30 GMT

Very slick piece of code!

This will definitely go into my utility directory.

Best,

Tom

Re: Validate delimiter used in a text file

Tom — Mon, 28 Dec 2015 00:09:03 GMT

You need to watch out for quoted delimiters and adjacent delimiters. Use the 'mq' modifier on COUNTW() function.

Try this example to see the differences.

filename sample1 temp;
data _null_;
 file sample1 ;
 put '1,,"this, is quoted",4,5'
   / '1,2,3,4,5'
   / '1,,3,4,5'
 ;
run;

data _null_;
  infile sample1 dsd truncover;
  length col1-col7 $20;
  input col1-col7;
  n1=countw(_infile_,',');
  n2=countw(_infile_,',','q');
  n3=countw(_infile_,',','mq');
  put _infile_ / (n: col:) (=) / ;
run;

Re: Validate delimiter used in a text file

aomy82 — Tue, 29 Dec 2015 17:51:52 GMT

Thanks PGStats, This solution worked for me with slight adjustments.

I really appreciate your prompt reply.