CODEWITHSUNDEEP
loopsmacrossas
Alexander
Alexander

Reputation: 644

Looping over libraries in SAS

I have thousands of files names CLICK, and they all reside inside different folder on my linux. I have assigned every single folder to a lib, and I am trying to extract each click file (and eventually append, while not in code shown below). This is what I have done

   `%let listlib=
A B C;

%macro char_loop();
%let i=1;
%let v=%scan(&listlib,&i);
%do %while(&v ne ) ;

data click&v;
     set &v.click;
     type = &v;
run;

%let i=%eval(&i+1);
%let v=%scan(&listlib,&i);
%end;
%mend;
%char_loop;`

However, it seems that SAS is not able to loop through "set &v.click;", thus is changing lib. The log says "WORK.ACLICK.DATA does not exist". What am I missing here?

Upvotes: 1

Views: 1248

Answers (2)

Joe
Joe

Reputation: 63424

&v. is the macro variable - & starts and . terminates. The . is not necessary if something else like a space or semicolon makes it obvious where the termination occurs, but it is technically a component. So you need

set &v..click

to get the actual period.

On a side note, SAS isn't really very good at this sort of thing. You'd be better off getting perl or something similar to collect the click files into one directory, or better yet combine them into one file (I've actually done this before with clickstream files). SAS isn't very efficient at opening and closing lots of individual files and will take a lot longer to do it.

Adding to the other answer here, if you're going to set them in one pass (which is a good idea), the best way is probably not to macro loop. You can more easily do it like this:

*macro to define an element of the set statement;
%macro set(lib=);
&lib..click
%mend set;

*proc sql to generate a list of these calls from dictionary.tables - make sure you do not have any tables you need excluded from this, and if so use WHERE to do so;
proc sql;
select cats('%set(lib=',libname,')')
into :setlist separated by ' '
from dictionary.tables
where memname='CLICK';
quit;

*set them;
data want;
set &setlist. indsname=indsn;
type = scan(indsn,1,'.');
run;

Usually, macro looping is more complicated, and slower, than doing it through regular old data steps and SQL. indsname works in 9.3+.

Upvotes: 2

user3272403
user3272403

Reputation: 106

as Joe pointed out the main issue was the missing second period ( &v..)

that said, it should be little quicker for you, if you set them on the initial read rather then making lots of work files to subsequent concatenate them together.

something like this should work:

*******************************************************************; 
*** a few test datasets.
***   note:  I prefixed that dataset with the libname because 
***          they are all technically in the same directory
***          and to highlight the difference between &v. and &v..
*******************************************************************; 
libname a (work); 
libname b (work); 
libname c (work); 

data a.aclick; 
 do i = 1 to 10; 
  output;
 end; 
run; 

data b.bclick; 
 do j = 1 to 10; 
  output;
 end; 
run; 

data c.cclick; 
 do k = 1 to 10; 
  output;
 end; 
run; 


*** modified macro  ****;    

%macro char_loop(listlib=);
%let ListN=%eval(%length(&listlib)-%length(%sysfunc(compress(&listlib)))+1); 
data click(drop=_i);
set
    %do i=1 %to &listN ;
     %let v=%scan(&listlib,&i);
       &v..&v.click (in=&v)
     %end; 
  ; 

array _t &listlib; 
do _i= 1 to &listN; 
  if _t(_i)=1 then type=vname(_t(_i)); 
end; 
run;

%mend;
%char_loop(listlib=a b c );
proc print data=click; 
run;

Upvotes: 2

Related Questions