macro function containing a proc sql

[DanJR]

Hi all, i am trying to create a macro which contains a proc sql procedure and returns a number. I would also like to call this macro within another macro.

Here's the macro:

%macro checkJoin(fromDSN, toDSN, linkVariable);
  %let obsLinkage = 0;
	proc sql noprint;
	  select count(*) 
		  INTO :obsLinkage
			FROM &toDSN T, &fromDSN F
			WHERE T.&linkVariable = F.&linkVariable;
	quit;
	&obsLinkage
%mend checkJoin;

Here's an example of how i'm calling it:

%macro testIt();
  %local theResult;
	&theResult = %checkJoin(data2, data1, id);
	%put &theResult;
%mend;

[tt]
When I call the macro, I get the following error:
select count(*) INTO bsLinkage FROM &toDSN T,
------
180
1 ! &fromDSN F WHERE T.&linkVariable = F.&linkVariable; quit; &obsLinkage

ERROR 180-322: Statement is not valid or it is used out of proper order.
[/tt]
I know how this can macro work if I get the macro to return the result to a global variable, but it would be nice to have it as a self contained 'macro function'. Is this possible?

Any help would be much appreciated
Cheers,
Dan

 

[klaz2002]

Dan,
I noticed that when you used the 'obsLinkage' macro var in your code you didn't preceed it with '&' char.
Try 'INTO:&obsLinkage' (notice the '&' char).

General info:
All macro does is substitute your text into a SAS program. You should test the code first as a reg SAS program. If the code runs, you can turn it into macro code very easily.

I hope that this helps you.
Klaz

 

[DanJR]

Hi Klaz2002,

Thanks for the reply. I'm fairly certain I tried using the & and it didn't work(?) - but I'll check once i'm back at work (monday) and let you know how it get on.

Thanks heaps,
Dan.

 

[DanJR]

Hi,

I tested my code as normal sas code and it works fine. The "INTO: bsLinkage" (without the &) is the correct syntax. However, I got my code working my using global variables rather than making a self-contained macro "function". In the meantime, i will read more about how sas compiles its code (in my spare time?).

Cheers,
Dan