Call to execute Macro without firm ending

[GustavSandberg]

Hi all!

I have, through various steps, created two sets of 4 tables;

BBB1, BBB2, BBB3 and BBB4

CCC1, CCC2, CCC3 and CCC4

I wish to put these tables together as such:

BBB1 + CCC1 = DDD1

BBB2 + CCC2 = DDD2

etc.

For this I have written a macro that works:

%macro sqlloop(start,end);

	PROC SQL;
		%DO no=&start. %TO &end.;
			CREATE TABLE DDD&no. as 
				SELECT * 
				FROM BBB&no., CCC&no.;
		%END;
	QUIT;

%mend;

%sqlloop(start=1, end=4)

My problem is that the previous code will not always create 4 tables. Might be 2, might be 41, it all depends on the input (however, there will always be an equal amount of BBBX-tables and CCCX-tables).

My question is; How do I write a call for execution of the macro, so that it goes through all tables, without having to manually put an exact number as "end"?

Best regards,

Gustav

[PaigeMiller · Accepted solution]

Use SQL to count the number of tables shown which have a name that begins with BBB

%macro sqlloop;
    proc sql noprint;
        select count(memname) into :num from dictionary.tables where libname='WORK' and memtype='DATA'
            and memname eqt "BBB";
    quit;
	PROC SQL;
		%DO no=1 %TO &num.;
			CREATE TABLE DDD&no. as 
				SELECT * 
				FROM BBB&no., CCC&no.;
		%END;
	QUIT;
%mend;

%sqlloop

[GustavSandberg]

Thank you very much for your fast answer and solution! It worked perfectly!

[GustavSandberg]

Hello again,

I have a very similar follow-up question to my previous question.

I have an unspecified number of tables (AAA1, AAA2, AAA3,...) that I want to transpose, and call them BBB1, BBB2,...etc. 

I am using the same solution to counting my AAA-tables, as suggested by the solution given here previously, which seems to work fine here too.

The Proc Transpose-part, however does not work. 

I get the following error messages:

NOTE: Line generated by the invoked macro "TRANSP".
52 data=AAA&no. out=BBB&no. (drop=_name_ _label_); id A; var B;
____
180

for each table.

And in the end:

ERROR 180-322: Statement is not valid or it is used out of proper order.

From what I understand, this error message is common when missing a semi-colon or similar. I have tried but not been able to fix it. Any suggestions?

%macro transp;

proc sql noprint;
        select count(memname) into :num from dictionary.tables where libname='WORK' and memtype='DATA'
            and memname eqt "AAA";
quit;

proc transpose
%DO no=1 %TO &num.;
data=AAA&no. out=BBB&no. (drop=_name_ _label_);
id A;
var B;
%END;
run;

%mend;

%transp