SAS- Error in automatic split all datasets in a library

Question

I have around 100 datasets in the "DATA" library. I used following code to split all datasets in a library.

Firstly, I use proc sql to put all dataset into a table and number them.
Secondly, I also use proc sql to read the content in each dataset in order to set rules for split. More specifically, the split process is based on two variable: date_l_ and _ric. Obviously, date_l_ is a date variable. And _ric is a variable that identify the name of stock. The results of this step is shown as following:

enter image description here .

Finally, I use %do j=1 %to &obs. to split the dataset.

However, I faced to the error as shown following:

I cannot find where this problem is coming from becuase I'm not name any dataset as WORK.SET.

%macro split(sourcelib=,from=,going=);
proc sql noprint;  /*read datasets in a library*/
  create table mytables as
  select *
  from dictionary.tables
  where libname = &sourcelib
  order by memname ;

  select count(memname) 
  into:numb 
  from mytables;

  %let numb=&numb.;

  select memname
  into : memname1-:memname&numb.
  from mytables;
quit;

  %do i=1 %to &numb.;
    proc sql noprint;
     create table tmp&i as
     select distinct date_l_, _ric
     from &from.&&memname&i;

     select count(*)
     into :obs
     from work.tmp&i;

     %let obs=&obs.;

     select date_l_, _ric, catx("_", substr(_ric, 1, 13), date_l_)
     into :date_l_1-:date_l_&obs., :ric1-:ric&obs., :setname1-:setname&obs.
     from work.tmp&i;
    quit;
   %end;
    data 
    %do j = 1 %to &obs.;
       &going.&&setname&j
    %end;
      ;
    %do i=1 %to &numb.;
      set &from.&&memname&i
    %end;
      ;/*may invoking i to numb.*/
      select;
    %do j = 1 %to &obs.;
      when(_ric = "&&ric&j" and date_l_ = &&date_l_&j) output  &going.&&setname&j;
    %end;
end;
%mend;

%split(sourcelib='AXP',from=AXP.,going=AXP.)

DomPazz DomPazz · Accepted Answer · 2016-10-25T16:02:44

You need a new loop for i

data 
%do i = 1 %to &obs.;   /*set rules for separated dataset*/
  &&setname&i
%end;
  ;
  set &source.&&memname&i;/*I assume this part as the core problem*/

You are not looping for i when you try to resolve that value. So i has the last value + 1 from the previous loop (i=1 to &obs).

I assume you actually want that data step inside the above loop (i=1 to &numb). In that case, change the loop over &obs to use something other than i (maybe j ?).

SAS- Error in automatic split all datasets in a library

1 Answers