DATA Step, Macro, Functions and more

Validate delimiter used in a text file

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Validate delimiter used in a text file

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. 


Accepted Solutions
Solution
‎12-29-2015 12:47 PM
Respected Advisor
Posts: 4,930

Re: Validate delimiter used in a text file

[ Edited ]

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.

PG

View solution in original post


All Replies
Super User
Posts: 5,435

Re: Validate delimiter used in a text file

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.
Data never sleeps
Occasional Contributor
Posts: 5

Re: Validate delimiter used in a text file

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.

Solution
‎12-29-2015 12:47 PM
Respected Advisor
Posts: 4,930

Re: Validate delimiter used in a text file

[ Edited ]

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.

PG
Occasional Contributor
Posts: 5

Re: Validate delimiter used in a text file

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

PROC Star
Posts: 1,167

Re: Validate delimiter used in a text file

Very slick piece of code!

 

This will definitely go into my utility directory.

 

Best,

  Tom

Super User
Super User
Posts: 7,074

Re: Validate delimiter used in a text file

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;
Occasional Contributor
Posts: 5

Re: Validate delimiter used in a text file

Thanks , This solution worked for me with slight adjustments.

 

I really appreciate your prompt reply.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 336 views
  • 5 likes
  • 5 in conversation