PROC SQL using list of tables in from

[liguang01]

Hello for all,

I'm new using SAS and PROC SQL.  I wrote a code to cartesian product with different values of 5 variables but i want to parameterize it.

%macro first_macro(size);
%let list_var = var1 var2 var3 var4 var5;
%do i=1 %to &size;
 %let var = %scan(&list_var,&i);
	proc sql;
	create table table&i as
	select distinct &var
	from file
	quit;
%end;

PROC SQL;Create Table cross_1 as Select monotonic() as id_5,* from table1,table2,table3,table4,table5;Quit;
PROC SQL;Create Table cross_2 as Select monotonic() as id_4,* from table1,table2,table3,table4;Quit;
PROC SQL;Create Table cross_3 as Select monotonic() as id_3,* from table1,table2,table3;Quit;
PROC SQL;Create Table cross_4 as Select monotonic() as id_2,* from table1,table2;Quit;
PROC SQL;Create Table cross_5 as Select monotonic() as id_1,* from table1;Quit;

%mend;


%first_macro(5);

There is one way to declare a list of tables separated by ',' and using it in proc sql sentence with loop?

thanks

[yabwon]

Hi,

do you mean use of macroquoting function, like %STR()

%macro first_macro(size, listOfFiles=);
%let list_var = var1 var2 var3 var4 var5;
%do i=1 %to &size;
 %let var = %scan(&list_var,&i);
	proc sql;
	create table table&i as
	select distinct &var
	from file
	quit;
%end;

PROC SQL;Create Table cross_1 as Select monotonic() as id_5,* from &listOfFiles.;Quit;
%mend;


%first_macro(5, listOfFiles=%str(table1,table2,table3,table4,table5));

All the best

Bart

[liguang01]

Yes but i want to create cross_1, cross_2... 

In the first iteration it will have to use table1--table5. In the second, it will have to use table1--table4...

[yabwon]

Hi,

Like that?

%macro first_macro(size, listOfFiles=);
%let list_var = var1 var2 var3 var4 var5;
%do i=1 %to &size;
 %let var = %scan(&list_var,&i);
	proc sql;
	create table table&i as
	select distinct &var
	from file
	quit;
%end;

%do i=1 %to &size;
  PROC SQL;
  create table cross_&i as 
  select monotonic() as id_&i ,* 
  from table1 
    %do j=2 %to &i;
      ,%qscan(&listOfFiles., &j, %str(,))
    %end; 
  ;
  Quit;
%end;

%mend;

%first_macro(5, listOfFiles=%str(table1,table2,table3,table4,table5));

If it could be a space separate list of tables you could do it without %str()

%macro first_macro(size, listOfFiles=);
%let list_var = var1 var2 var3 var4 var5;
%do i=1 %to &size;
 %let var = %scan(&list_var,&i);
	proc sql;
	create table table&i as
	select distinct &var
	from file
	quit;
%end;

%do i=1 %to &size;
  PROC SQL;
  create table cross_&i as 
  select monotonic() as id_&i ,* 
  from table1 
    %do j=2 %to &i;
      ,%qscan(&listOfFiles., &j, %str( ))
    %end; 
  ;
  Quit;
%end;

%mend;

%first_macro(5, listOfFiles=table1 table2 table3 table4 table5);

All the best

Bart

[liguang01]

It is exact what i want thanks. 

is  there is possible that you dont specify list of tables and the program catch it by size (parameter of macro) ?

[yabwon · Accepted solution]

Yes,

%macro first_macro(size);
%let list_var = var1 var2 var3 var4 var5;
%do i=1 %to &size;
 %let var = %scan(&list_var,&i);
	proc sql;
	create table table&i as
	select distinct &var
	from file
	quit;
%end;

%do i=1 %to &size;
  PROC SQL;
  create table cross_&i as 
  select monotonic() as id_&i ,* 
  from table1 
    %do j=2 %to &i;
      ,table&j.
    %end; 
  ;
  Quit;
%end;

%mend;

%first_macro(5);

All the best

Bart

[Tom]

DON"T use comma as the delimiter in your macro code. 

You can have the macro put in the commas where they are needed.

For example you could loop loop over the list of variables and then loop over the list of tables.

%macro mymacro(varlist,tablelist);
%local i j var sep;
proc sql;
%do i=1 %to %sysfunc(countw(&varlist,%str( )));
  %let var = %scan(&varlist,&i,%str( ));
create table table&i as
  select distinct &var
  from 
  %let sep=;
  %do j=1 %to %sysfunc(countw(&tablelist,%str( )));
     &sep.%scan(&tablelist,&j,%str( ))
    %let sep=,;
  %end;
;
%end;
quit;
%mend mymacro;
%mymacro(varlist=var1 var2 var3,tablelist=table1 table2 table3)