topic Re: Can we use macro to check how many variables the files have (With pipe) in SAS Programming

Can we use macro to check how many variables the files have (With pipe)

Bal23 — Fri, 14 Apr 2017 20:18:50 GMT

C:\ab\cd\filename DIRLIST pipe 'dir "C:\ab\cd\*.txt" /b ';

data dirlist ;
   infile dirlist lrecl=200 truncover;
   input file_name $100.;
run;

****Part I; old file;

data _null_;
   set dirlist end=end;
   count+1;
   call symputx('read'||put(count,4.-l),cats('C:\ab\cd\',file_name));
   call symputx('dset'||put(count,4.-l),scan(file_name,1,'.'));
   if end then call symputx('max',count);
run;

options mprint symbolgen;
%macro readin;
   %do i=1 %to &max;

      data &&dset&i;
         infile "&&read&i" lrecl=1000 truncover dsd;
         input var1 $ var2 $ var3 $;
       run;

   %end;
%mend readin;

%readin;

The limitation is that it can show limited amount of variables. If I am not sure how many variables they have, I want to use another loop.
options mprint symbolgen;

******part II, new one; I want to replace the part I with code below by adding &j so that I need not know how many variables each file has, need not type var1, var2, var3, etc;


%macro readin;
   %do i=1 %to &max;
     %do j=1 %to &max;

      data &&dset&i;
         infile "&&read&i" lrecl=1000 truncover dsd;
         input var&j $ 
       run;

   %end;
  %end;
%mend readin;

%readin;

Re: Can use macro to check how many variables the files have

Reeza — Fri, 14 Apr 2017 18:46:03 GMT

I don't know what your question is.

Re: Can use macro to check how many variables the files have

ballardw — Fri, 14 Apr 2017 18:56:41 GMT

If you don't know what is your text files you may want to look at PROC IMPORT to bring data into SAS.

Set a largish value for the guessingrows option.

Re: Can use macro to check how many variables the files have

Bal23 — Fri, 14 Apr 2017 19:22:34 GMT

sorry I have not made it clear

I want to replace my old sas program with new one, by introducing another macro &j

other wise, you can see from my previous sas program, i typed, var1 var2 var3, I want to replace this with var&j, but it does not work as I expect. so i hope you can check my second part of my sas code and give advice

thank you

Re: Can use macro to check how many variables the files have

Tom — Fri, 14 Apr 2017 19:45:15 GMT

Do you want to read all of the txt files in a directory? If so there is no need for macro varaibles to do this. Are you just counting how many columns are in each txt file? Can we assume that the each row has the same number of fields? Or do you want to find the maximum over the whole file? So perhaps something like this?

data want ;
  length fname filename $255 ;
  infile "C:\ab\cd\*.txt" dsd truncover filename=fname end=eof column=col length=len;
  input @;
  do n=1 by 1 while (col <= len) ;
    input word :$50. @;
  end;
  input;
  if fname ne lag(fname) then do ;
    if _n_ > 1 then output;
    filename = fname ;
    maxn=n ;
  end;
  else maxn=max(maxn,n);
  if eof then output;
  retain maxn filename ;
  keep filename maxn ;
run;

Re: Can use macro to check how many variables the files have

Bal23 — Fri, 14 Apr 2017 19:53:13 GMT

@Tom,

ERROR 161-185: No matching DO/SELECT statement.

ERROR 160-185: No matching IF-THEN clause.

I got error messages with you code.

What I want to do is I want to replace my part I code with my part II code, by add one more macro &j. It does not work. I am open to other options as well. Thanks.

Re: Can use macro to check how many variables the files have

Reeza — Fri, 14 Apr 2017 20:25:18 GMT

@Bal23 When editing your original post please indicate what's changed. Please remember that although people are trying to help you answer your question, this is also to create a body of knowledge that other users can use in the future to help answer their question.

Changing your original post after answers have been posted confuse this especially since there's no history for a post to trace edits.

Re: Can use macro to check how many variables the files have

Bal23 — Fri, 14 Apr 2017 20:36:32 GMT

@Reeza

I added comments, marked one as part i and the other as part II. I do not change any code. I wanted to make it clear and did not intend to confuse those who want to help. Thank you and sorry that confuses you.

Re: Can use macro to check how many variables the files have

Tom — Fri, 14 Apr 2017 21:48:52 GMT

Your current program is saying for the 5th file you should read one variable and name it VAR5.

Is that what you want?

I would assume that what you want to is have a series of number N1, N2,.... and so for the Jth file you would want to read variables VAR1 to VAR&&n&j.

How do you intend find out these N1, N2, ... numbers?

Re: Can we use macro to check how many variables the files have (With pipe)

Tom — Sat, 15 Apr 2017 03:49:00 GMT

So you can read the files and figure out how many columns each one has.

data filelist ;
  length fname filename $255 memname $32 ;
  infile "&path\*.txt" dsd truncover filename=fname end=eof column=col length=len;
  input @;
  do n=1 by 1 until (col > len) ;
    input word :$50. @;
  end;
  input;
  if fname ne lag(fname) then do ;
    if _n_ > 1 then output;
    filename = fname ;
    memname = scan(scan(filename,-1,'/\'),1,'.');
    maxn=n ;
  end;
  else maxn=max(maxn,n);
  if eof then output;
  retain maxn filename memname ;
  keep filename memname maxn ;
run;

Then if you want you can push all of that data into multiple macro variables.

data _null_;
  if eof then call symputx('nfiles',_n_-1);
  set filelist end=eof;
  call symputx(cats('read',_n_),filename);
  call symputx(cats('dset',_n_),memname); 
  call symputx(cats('n',_n_),maxn);
run;

So that you could make a macro that loops over all of the files and generates code to read each one into a dataset.

%macro readin;
%do i=1 %to &nfiles;
data &&dset&i;
  infile "&&read&i" truncover dsd;
  input (var1-var&&n&i) (:$10.) ;
run;
%end;
%mend readin;

%readin;

But why not just generate the code directly from the data instead of making all of those macro variables and having to create a macro?

filename code temp;
data _null_;
  set filelist ;
  file code ;
  put 'data ' memname ';' 
    / '  infile ' filename :$quote. 'truncover dsd;'
    / '  input (var1-var' maxn ') (:$10.);'
    / 'run;'
  ;
run;
%include code / source2 ;