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
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
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: