stack datasets in proc sql using a do loop

[gleebglorb]

	
%macro bowling(alley);
proc sql; 
%let nwords=%sysfunc(countw(&alley,%str( , )));

	create table as (
	select * from (
		%do j = 1 %to &nwords;

		%let alley = scan(j,&alley);
			select( *, "&alley" as allnm from
			bowling )
			%if &j ne &nwords %then %do; 
				union
			%end;
		%end;
)
;
	quit;

In a single query, I am trying to pull information from several datasets in a macro and stack them all on top of each other.  
The macro will take in a single argument, alley, which will provide a list of bowling alleys.  Next, the macro should look up information from a dataset called &alley.sas (ie: if the alley name is Henderson, it's info will be in a dataset called Henderson.sas).  I am trying to loop through the list of alleys given, which is what the nwords macro is for, and stack all of that information on each other.  When it gets to the alley name in the list, it should end.  obviously this macro logic is flawed and there are probably several syntax errors, but this is a rough draft for what I am trying to achieve

[Kurt_Bremser]

Don't waste your time trying to do this in SQL, the tool for this is a data step.

%macro bowling(alley);
data want;
length inname $41 allnm $32;
set
%do i = 1 %to %sysfunc(countw(&alley.));
  library.%scan(&alley.,&i.)
%end;
  indsname=inname
;
allnm = scan(inname,2,'.');
run;
%mend;

Replace "library" with your real library name.

[maguiremq]

Maxim 14!!!

[gleebglorb]

Is it possible to do in SQL? 

[Reeza]

---

@gleebglorb wrote:

Is it possible to do in SQL? 

---

Yes, but that's like using a hammer on a thumbtack. 

[Patrick]

Here just to demonstrate that it can be done using SQL - which might be useful when generating code that can execute fully in-database.

Unlike with a data step for the generated SQL to work all tables must have the exactly same columns (same number and names). If that's not the case then you will need to add an additional parameter for listing the variable names you want to use (but: they still need to exist in all source tables).

data sampleA sampleB sampleC;
  set sashelp.class;
run;

data control;
  input (alley ds) (:$41.);
  datalines;
alleyA work.sampleA
alleyB work.sampleB
alleyC work.sampleC
;

%macro demo(alleys,control_ds,out_ds);
  filename codegen temp;
  data _null_;
    file codegen;
    set &control_ds end=last;
    if findw("&alleys",strip(alley),' ','i') then
      do;
        _found+1;
        if _found=1 then
          put 
            "proc sql;" /
            "  create table &out_ds as"
            ;
        if _found>1 then
          put "  union corr all";
        put 
          "  select '" ds +(-1) "' as inds length=41,*" /
          "  from " ds /
          ;
      end;
    if last and _found>0 then 
      put "quit;";
  run;

  %include codegen /source2;
  filename codegen clear;
%mend;

%demo(alleya  alleyc,control,stacked)

[Reeza]

Do you have a naming convention of any sort?
If so, it maybe easier to use a shortcut list in a data step?

Here is a reference that illustrates how to refer to variables and datasets in a short cut list:
https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html