BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
GustavSandberg
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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

 

--
Paige Miller

View solution in original post

3 REPLIES 3
PaigeMiller
Diamond | Level 26

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

 

--
Paige Miller
GustavSandberg
Calcite | Level 5
Thank you very much for your fast answer and solution! It worked perfectly!
GustavSandberg
Calcite | Level 5

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

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 336 views
  • 0 likes
  • 2 in conversation