Make code run on loop

[aloou]

Hello everyone,

please i need help on this little problem.

I have a number of tables Under libref X, i created libref Y.

proc sql;

create table Y.table-name as

select * from X.table-name

having Period=(max(Period));

quit;

all i want is to run this code on all the hundred of tables Under libref X.

i will be so thankfull.

[PaigeMiller · Accepted solution]

/* Untested Code */

proc sql noprint;
    select memname into :dsnames separated by ' ' from dictionary.tables;
quit;

%macro do_all;
    %do i=1 %to %sysfunc(countw(&dsnames));
        %let thisname=%scan(&dsnames,&i,%str( ));
        proc sql;
           create table y.&thisname as select * from x.&thisname
                having period=max(period));
         quit;
    %end;
%mend;
%do_all

 Limitation: the macro variable that is created cannot be more than around 65000 characters. If it is more that that, you would need to use CALL EXECUTE.

[aloou]

Dear Sir,

i have tried your code and partly it is working, however the problem is that dsnames is getting all table names in one line ,

so when the proc sql work it will take dsnames as a whole name and won't consider them as separated names

Thank you so much

[PaigeMiller]

First, use OPTIONS MPRINT; as the first line of the program and then run it again.

You will have to show us the LOG (the entire log, code and error message). To show us the log, click on the {i} button (DO NOT SKIP THIS STEP) and paste the LOG into the window that appears; this will preserve the formatting of the log and make it readable.

[aloou]

i brought some changes to the code that you gave me and now it is working well.

i am so grateful for your help.

[Tom]

First you need to get the list of the datasets (what you called tables) that you want to copy.

There are many ways. An easy one is to use PROC CONTENTS.

proc contents data=X._all_ noprint out=contents; run;

For your code to work the dataset must contain the variable PERIOD. So let's limit the list to just those datasets. The name of the dataset is in the variable MEMNAME and the name of the variable is in the variable NAME.

data list;
  set contents;
  where upcase(name)='PERIOD';
  keep memname ;
run;

Now you just need to use that list to generate a separate SQL statement for each dataset.  You could try to use macro code or CALL EXECUTE. Personally I prefer to use PUT statement to write the code to a file.  That is easier to debug since you can look at the file and make sure you have generated the right code.

filename code temp;
data _null_;
  set list;
  file code;
  put 'create table Y.' memname 'as select * from X.' memname 
      'having Period=(max(Period));'
  ;
run;

Then just use %INCLUDE to run the code in the file.

proc sql;
%include code;
quit;