BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
aomy82
Fluorite | Level 6

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

7 REPLIES 7
LinusH
Tourmaline | Level 20
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
aomy82
Fluorite | Level 6

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.

PGStats
Opal | Level 21

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
aomy82
Fluorite | Level 6

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

TomKari
Onyx | Level 15

Very slick piece of code!

 

This will definitely go into my utility directory.

 

Best,

  Tom

Tom
Super User Tom
Super User

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;
aomy82
Fluorite | Level 6

Thanks , This solution worked for me with slight adjustments.

 

I really appreciate your prompt reply.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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