Executing Macro Functions from Macro Variables. Impossible?

[TomekO]

I have an SQL statement in SAS which generates many variables from a large dataset. In some cases, I only want some of the variables to generate. I could easily put %IF %THEN conditions around each line / blocks of lines inside the macro, However, I was hoping to make my code less messy by having the %IF %THEN inside a macro variable

&AA.   ,sum(debt) as debt        &BB.

instead of 

%IF &TVAR. = Y %THEN %DO;     ,sum(debt) as debt     %end;

However, when I try to run such code, I get aSyntax  error:

NOTE: Line generated by the macro variable "AA".
36          %if &TVAR. = Y %then %do;

Here is an example of the code that should work but doesn't. Is SAS simply unable to resolve %IF %THEN macro functions from Macro varibales? 

%let TVAR = Y;
%let AA = %nrstr(%if &TVAR. = Y %then %do;);
%let BB = %nrstr(%end;);
%put &AA.;

%macro ttestm;
    Data Test; A = 5; run;
    proc sql;
        create table Test2 as
        select
        1 as A
        ,2 as B
        &AA. ,3 as C &BB.
        from Test
    ;quit;
%mend;
%ttestm;

There are workarounds I can create but i'd rather have &AA. instead of long %IF &THENs all over the place. 

Thanks

Tomek

EDIT:

I need to add that the above code is an example. In reality i have code like:

,sum(case when debt_age is between 0 and 3 then debt else 0 end) as Debt3m
,max(write_off date) as latest_wod
,sum(case when payment > 0 and DOCTYPE = 'AR' then BILL else 0 end) as latest_payment

Again, this is example code, but when you have tens of these types of variables and you need some to run always, and others to only run sometimes, I wanted neat code. However, It seems like SAS simply can't execute macro code from a variable. 

[ad123123]

Hi,

You can use proc sql case expression. Here is a link to modify you code:-

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/a002473682.htm

Please try following code:-

%let TVAR='Y';
%put &TVAR.;
%macro ttestm;

Data Test;
A = 5;
run;

proc sql;
create table test2 as
select 1 as A,
2 as B,
case
when &TVAR. = 'Y' then 50
else 100
end as C
from test;
quit;

%mend;

%ttestm;

I hope it help you.

Regards,

ABD

[TomekO]

That isn't exactly what I'm looking for. My example code makes my issue seem simpler than it is.

I have complex sums with case whens inside loops which create hundreds of variables.

This is why adding macro code around each of these would be best and simples, as opposed to incorporating an overlaying case when. It'd be better to just have the full %IF %THEN code instead. 

[Ksharp]

(Maybe you need put %unquote() around it :

%macro ttestm;
    Data Test; A = 5; run;
    proc sql;
        create table Test2 as
        select
        1 as A
        ,2 as B
       %unquote( &AA. ) ,3 as C  %unquote(&BB.)
        from Test
    ;quit;
%mend;

And or you could use IFC() 

%let TVAR = Y;
%let AA = %sysfunc(ifc(&TVAR. = Y ,%str(,sum%(debt%) as debt) , ));
%put %unquote(&AA.);

[TomekO]

Already tried that. The Error cahnges to:

ERROR: The %IF statement is not valid in open code.
ERROR: The %END statement is not valid in open code.

As for the other suggestion, that would require me to change a lot of code so not ideal for me.

[Kurt_Bremser]

That just says you have a %if and %end statement outside of a macro definition. Conditions and %do work only within a macro, not in open code.

As %... keywords are interpreted by the macro engine immediately, you cannot use them to build a macro variable outside of the context of a macro definition.

[Kurt_Bremser]

Another question: why, in $DEITY's name, are you doing that? Putting the %if statement into a macro variable that is then used within a macro to dynamically create a macro statement is a clear attempt at winning the SAS obfuscated code contest.

Rather follow the KISS principle, and write the statement where it shall be executed.

[ad123123]

Hi,

Please check if following logic is helpful:-

%let TVAR = Y;
%let col1 = ,3 as c;
%let col2 = ,4 as d;
%let col3 = ,5 as e;


%macro ttestm;
	%let i = 1;
    Data Test; A = 5; run;
    proc sql;
        create table Test2 as
        select
        1 as A
        ,2 as B
		%do %while (&i <= 3);
		
			%if &i = 3 %then %let TVAR = N;

			%if &TVAR = Y %then 
			  &&col&i.;	
		%let i = %eval(&i + 1);
		%end;


        from Test

    ;quit;
%mend;
%ttestm;

[RW9]

Can you post test data in the form of a datastep and required output.  I don't see why you would need code like that to do this, base SAS and arrays should be sufficient.  Also, why do you want a wide table, coding using a long table is far easier, and simpler to code/process.

[Astounding]

You are making things difficult by trying to use a macro variable containing %IF/%THEN.  Instead, define a macro for each variable that is optional.  For example:

%macro debt (use=Y);

   %if &use=Y %then %do;

      , sum(debt) as debt

   %end;

%mend debt;

Then in your SELECT statement, you could add any of these:

%debt

%debt (use=Y)

%debt (use=N)

You just need to define a macro instead of a macro variable, and it's easy to read the code if you name the macro after the variable that it creates.

Good luck.

[TomekO]

I need to add that the above code is an example. In reality i have code like:

,sum(case when debt_age is between 0 and 3 then debt else 0 end) as Debt3m
,max(write_off date) as latest_wod
,sum(case when payment > 0 and DOCTYPE = 'AR' then BILL else 0 end) as latest_payment

Again, this is example code, but when you have tens of these types of variables and you need some to run always, and others to only run sometimes, I wanted neat code. However, It seems like SAS simply can't execute macro code from a variable. 

[RW9]

Can I ask, why do these sometimes get done and sometimes not?  Identify what you need, and what cases you don't need certain things, and have processes for each.

[TomekO]

Certainly. Sometimes, I want to run all the statements, like when building a new predictive model so I can look at all available variables.

Other times, I want to run only certain lines, the one's necessary for existing models. Perhaps different options later on too so several if and/or then statements. Would be nice to have them stored in a short macro variable, rather than having an IF then on all the lines.

All this I wish to be based on a Macro variable at the start.

[Kurt_Bremser]

Then I would write a macro like

%macro insert_va(var,as,cond);
%if &cond = 1 %then %do;
, &var as &as
%end;
%mend;

Now you can write the select part of your SQL as a succession of macro calls, using different conditions for different variable groups. Set the conditions as macro variables before the SQL, and the rest automates itself. And you avoid complex code.

Another version of the macro might be

%macro insert_va(var,as,condcode);
%if &global_cond = all or &globalcode = &condcode %then %do
, &var as &as
%end;
%mend;

This lets you set a specific condition for each variable, and if &global_cond is set to all (note no quotes), all variables are included.

[Kurt_Bremser]

To make things easier, I'd use a data step and call execute to build the SQL or whatever you need to do. Once the basic problems of building program code with call execute are mastered, you can then use all the tools of the data step to automate the building of the code, using a control data set.

This will take a lot of work in the beginning, but later on you only need to maintain your control data set.

Keep in mind that the macro language as such is meant to automate other forms of SAS code. Using it to automate itself will end up being very tricky.