topic Re: Count delimter (typically gives me header count) in SAS Programming

Count delimter (typically gives me header count)

SASPhile — Tue, 23 May 2017 20:05:37 GMT

There are 50k files. I would like to check the number of columns in each file . The raw files are text files. I would like to see the the file name, number of columns in a temp dataset before actually reading them into SAS datasets. The delimiter is tab separated.

Any help is appreciated!

Re: Count delimter (typically gives me header count)

Reeza — Tue, 23 May 2017 20:06:32 GMT

Ok...so what's your question?

Re: Count delimter (typically gives me header count)

SASPhile — Tue, 23 May 2017 20:12:58 GMT

How to get the header count

Re: Count delimter (typically gives me header count)

ballardw — Tue, 23 May 2017 20:15:35 GMT

So what will you actually do with the approximately 625 pages of results?

Are all of the text files in the same folder? Mulitple folders? Is there any regular pattern to the names of the files?

Re: Count delimter (typically gives me header count)

Reeza — Tue, 23 May 2017 20:16:19 GMT

1. Either process the first line of each file - read it in using filevar and obs=1 and then use COUNTW to count the number of the delimiter

2. Use Powershell/Unix command lines to get this information.

#2 will be more efficient, faster and is most likely the right tool for the job.

SAS can do it, but it's a hammer vs thumbtack.

Re: Count delimter (typically gives me header count)

SASPhile — Tue, 23 May 2017 20:21:02 GMT

Same folder.

abc.txt

pqr.txt

xyz.txt

File_name header_cnt

abc.txt 25

pqr.txt 26

xyz.txt 30

Re: Count delimter (typically gives me header count)

Reeza — Tue, 23 May 2017 20:22:31 GMT

1. Get a list of files - see SAS 9.4 macro appendix on how that can be done automatically or search on here for many various solutions. Pretty sure I posted one last week.

2. Use a datastep to read a single line from a single file and count the number of delimiters (infile, COUNTW). Make sure it works for one file CORRECTLY before moving forward.

3. Look at FILEVAR option that allows you read multiple files based on paths, which you have from step 1

4. Combine 3/4 so that it will process all your files

Re: Count delimter (typically gives me header count)

BrunoMueller — Tue, 23 May 2017 20:43:45 GMT

First create a data set that holds the list of files to check, there are different approches to that, like FILENAME statement with the PIPE access method.

Once you have the list you can use a second DATA Step to read the first line of each file, Have a look at the FILEVAR= option of the INFILE statement. Using just and empty INPUT; statement read the first line. Then you can use the COUNTW function to count the number of "words" delimited by "09"x (hexadecimal for TAB char).

Bruno

Re: Count delimter (typically gives me header count)

s_lassen — Wed, 24 May 2017 09:11:41 GMT

@SASPhile: I would try something like this:

%let dir=/usr/local/tmp; /* full path of directory with files */
data members;
  did=dopen('indir');
  do _n_=1 by 1;
    fname=dread(did,_N_);
    if fname=' ' then leave;
    /* you can also put criteria here, instead of in the next datastep, 
       only LIKE is very nifty and only available with WHERE */
    output;
    end;
  keep fname;
run;

data hcount;
  set members;
  where upcase(fname) like '%.TXT'; /* or whatever criteria you want */
  path=cats("&dir",'/',fname); /* may have to change / to \ for Windows */
  infile dummy filevar=path;
  input;
  count=count(_infile_,'09'x);
  keep fname count;
run;

Re: Count delimter (typically gives me header count)

Tom — Wed, 24 May 2017 09:57:53 GMT

If you can use PIPE from your SAS session (some server installations have this disabled) then use that to get the list of files and then use FILEVAR option to open each one. Here is example using Linux syntax. For DOS/Windows systems change 'ls -d' to 'dir /b'.

%let path=/myfolder;
data want ;
   length filename $100 header_cnt 8 filevar $255 ;
   infile "cd &path ; ls -d *.txt" pipe truncover ;
   input filename ;
   filevar=catx('/',"&path",filename);
   infile header filevar=filevar end=eof;
   if eof then header_cnt=0;
  else do;
    input ;
    header_cnt=countw(_infile_,'09'x,'mq');
  end;
run;

Otherwise you could use a wildcard in a regular INFILE statement with the FILENAME option, but then you will need to read all of the lines of all of the lines. Should work for either Linux or DOS/Windows systems.

%let path=/myfolder;
data want ;
   length filename $100 header_cnt 8 filevar $255 ;
   infile "&path/*.txt" eov=eov filename=filevar truncover ;
   input ;
   if _n_=1 or eov then do;
     filename=scan(filevar,-1,'/\');
     header_cnt=countw(_infile_,'09'x,'mq');
     output;
  end;
  eov=0;
run;

Re: Count delimter (typically gives me header count)

s_lassen — Wed, 24 May 2017 10:57:22 GMT

@Tom: I would probably use the COUNT function rather than the COUNTW function - when parsing .csv files, two consecutive delimiters means that there is a column, but it is empty in that row. Using COUNTW could give you the number of non-empty columns in the row being parsed, not the total number of columns in the file.

Re: Count delimter (typically gives me header count)

Tom — Wed, 24 May 2017 13:44:57 GMT

The M and Q modifiers on the COUNTW() function handle the missing values. Note that the COUNT() function cannot handle cases where the value can contain the delimiter.

Re: Count delimter (typically gives me header count)

SASPhile — Wed, 24 May 2017 14:13:13 GMT

I believe in this statement did=dopen('indir'), it should be 'dir' in place of 'indir'?

Re: Count delimter (typically gives me header count)

SASPhile — Wed, 24 May 2017 14:39:34 GMT

this code if it is run once,it is working. if I had to run the second time by adding more files to the directory, I'm getting an error.

" ERROR: File is in use,"

%let path=/myfolder;
data want ;
   length filename $100 header_cnt 8 filevar $255 ;
   infile "&path/*.txt" eov=eov filename=filevar truncover ;
   input ;
   if _n_=1 or eov then do;
     filename=scan(filevar,-1,'/\');
     header_cnt=countw(_infile_,'09'x,'mq');
     output;
end;
eov=0;
run;

Re: Count delimter (typically gives me header count)

Tom — Wed, 24 May 2017 15:10:40 GMT

If it worked once but now you are getting 'file in use' error messages then most likely the issue is that you have one or more of the individual files open with some other application.

Re: Count delimter (typically gives me header count)

s_lassen — Wed, 24 May 2017 15:25:17 GMT

Sorry, forgot a line in the program, the filename statement:

%let dir=/usr/local/tmp; /* full path of directory with files */
Filename indir "&dir";
data members;
  did=dopen('indir');
  do _n_=1 by 1;
    fname=dread(did,_N_);
    if fname=' ' then leave;
    /* you can also put criteria here, instead of in the next datastep, 
       only LIKE is very nifty and only available with WHERE */
    output;
    end;
  keep fname;
run;

data hcount;
  set members;
  where upcase(fname) like '%.TXT'; /* or whatever criteria you want */
  path=cats("&dir",'/',fname); /* may have to change / to \ for Windows */
  infile dummy filevar=path;
  input;
  count=count(_infile_,'09'x);
  keep fname count;
run;