Error in automatic split all datasets in a library- SAS Programmer

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Error in automatic split all datasets in a library- SAS Programmer

[ Edited ]

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

  1. Firstly, I use proc sql to put all dataset into a table and number them.
  2. 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:

name of variable

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

However, I faced to the error as shown following:

20161031001154.png

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.)

 


Accepted Solutions
Solution
‎11-01-2016 12:14 PM
Esteemed Advisor
Esteemed Advisor
Posts: 7,253

Re: Error in automatic split all datasets in a library- SAS Programmer

I would agree with the other posters, spliting a dataset really isn;t a good way forward.  If you need to split for transfer to Excel or something then do it in your report/export, but you will find your coding becomes so much more complicated if you try to work with data like that.  Anyways a simpler example:

data a;
  date_i_="01JAn2013"d; ric="abc"; val=1; output;
  date_i_="05JAn2013"d; ric="abc"; val=2 ;output;
  date_i_="15JAn2013"d; ric="def"; val=3 ;output;
run;
data b;
  date_i_="01JAn2013"d; ric="xyz"; val=4; output;
  date_i_="05JAn2013"d; ric="zyd"; val=5 ;output;
  date_i_="15JAn2013"d; ric="zyd"; val=6 ;output;
run;

%macro Split (ds=);
  proc sort data=work.&ds. out=tmp nodupkey;
    by date_i_ ric;
  run;
  data _null_;
    set tmp;
    call execute(cats('data work.',ric,'_',put(date_i_,date9.),"; set work.&ds.; where date_i_=",date_i_,' and ric="',ric,'"; run;'));
  run;
%mend Split;

data _null_;
  set sashelp.vtable (where=(libname="WORK"));
  call execute(cats('%Split (ds=',memname,');'));
run;

View solution in original post


All Replies
Super User
Posts: 1,256

Re: Employment Opportunity- SAS Programmer

Add options mprint;  before

   

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

adding also the semicolon at the end of %split line. 

 

Then, if error still persist, post log again.

Occasional Contributor
Posts: 15

Re: Employment Opportunity- SAS Programmer

I updated the error messages
Respected Advisor
Posts: 5,005

Re: Error in automatic split all datasets in a library- SAS Programmer

You have a loop that generates the wrong SET statement:

 


    %do i=1 %to &numb.;
      set &from.&&memname&i
    %end;
      ;/*may invoking i to numb.*/

 

This repeats the word SET each time through the loop, making SAS think that you are trying to read from a data set named SET. 

Occasional Contributor
Posts: 15

Re: Error in automatic split all datasets in a library- SAS Programmer

And how can I solve this problem?
Respected Advisor
Posts: 5,005

Re: Error in automatic split all datasets in a library- SAS Programmer

Only you can answer that.  What should the SAS program look like, after macro language is done generating the code?

 

You could always take the word SET out of the loop, the way you did with the prior loop that generates the DATA statement.  But there is no guarantee that the resulting program is correct ... only a guarantee that you will have eliminated the source of the current error.  To get the entire program working correctly, you have to picture what the SAS language program should look like, and instruct macro language in a way that generates the proper program.

Grand Advisor
Posts: 10,251

Re: Error in automatic split all datasets in a library- SAS Programmer

Instead of

 

 %do i=1 %to &numb.;
      set &from.&&memname&i
    %end;

You want something like

   Set

%do i=1 %to &numb.;
      &from.&&memname&i
%end;
; /*<= this semicolon is the one that ends the SET statement*/

 

Grand Advisor
Posts: 17,464

Re: Error in automatic split all datasets in a library- SAS Programmer

Generally separating data sets isn't suggested. 

 

However, if you're going to do it, consider a method similar to the one outlined in this post. It's easier to understand. 

 

http://blogs.sas.com/content/sasdummy/2015/01/26/how-to-split-one-data-set-into-many/

Grand Advisor
Posts: 9,596

Re: Error in automatic split all datasets in a library- SAS Programmer


data ;  /*<--------------*/
 set DATA.DATA1 ..........

should specify a dataset name or keyword

data _null_;
 set DATA.DATA1 ............
Solution
‎11-01-2016 12:14 PM
Esteemed Advisor
Esteemed Advisor
Posts: 7,253

Re: Error in automatic split all datasets in a library- SAS Programmer

I would agree with the other posters, spliting a dataset really isn;t a good way forward.  If you need to split for transfer to Excel or something then do it in your report/export, but you will find your coding becomes so much more complicated if you try to work with data like that.  Anyways a simpler example:

data a;
  date_i_="01JAn2013"d; ric="abc"; val=1; output;
  date_i_="05JAn2013"d; ric="abc"; val=2 ;output;
  date_i_="15JAn2013"d; ric="def"; val=3 ;output;
run;
data b;
  date_i_="01JAn2013"d; ric="xyz"; val=4; output;
  date_i_="05JAn2013"d; ric="zyd"; val=5 ;output;
  date_i_="15JAn2013"d; ric="zyd"; val=6 ;output;
run;

%macro Split (ds=);
  proc sort data=work.&ds. out=tmp nodupkey;
    by date_i_ ric;
  run;
  data _null_;
    set tmp;
    call execute(cats('data work.',ric,'_',put(date_i_,date9.),"; set work.&ds.; where date_i_=",date_i_,' and ric="',ric,'"; run;'));
  run;
%mend Split;

data _null_;
  set sashelp.vtable (where=(libname="WORK"));
  call execute(cats('%Split (ds=',memname,');'));
run;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 419 views
  • 0 likes
  • 7 in conversation