topic Re: Executing Macro Functions from Macro Variables. Impossible? in SAS Programming

Executing Macro Functions from Macro Variables. Impossible?

TomekO — Thu, 21 Jan 2016 14:55:55 GMT

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.

Re: Executing Macro Functions from Macro Variables. Impossible?

ad123123 — Thu, 21 Jan 2016 05:22:34 GMT

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

Re: Executing Macro Functions from Macro Variables. Impossible?

TomekO — Thu, 21 Jan 2016 05:56:54 GMT

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.

Re: Executing Macro Functions from Macro Variables. Impossible?

Ksharp — Thu, 21 Jan 2016 06:05:58 GMT

(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.);

Re: Executing Macro Functions from Macro Variables. Impossible?

TomekO — Thu, 21 Jan 2016 06:09:30 GMT

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.

Re: Executing Macro Functions from Macro Variables. Impossible?

Kurt_Bremser — Thu, 21 Jan 2016 06:45:18 GMT

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.

Re: Executing Macro Functions from Macro Variables. Impossible?

Kurt_Bremser — Thu, 21 Jan 2016 06:49:12 GMT

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.

Re: Executing Macro Functions from Macro Variables. Impossible?

ad123123 — Thu, 21 Jan 2016 06:58:37 GMT

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;

Re: Executing Macro Functions from Macro Variables. Impossible?

RW9 — Thu, 21 Jan 2016 09:55:31 GMT

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.

Re: Executing Macro Functions from Macro Variables. Impossible?

Astounding — Thu, 21 Jan 2016 14:18:03 GMT

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.

Re: Executing Macro Functions from Macro Variables. Impossible?

TomekO — Thu, 21 Jan 2016 14:55:35 GMT

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

Re: Executing Macro Functions from Macro Variables. Impossible?

RW9 — Thu, 21 Jan 2016 15:33:00 GMT

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.

Re: Executing Macro Functions from Macro Variables. Impossible?

Kurt_Bremser — Thu, 21 Jan 2016 16:37:58 GMT

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.

Re: Executing Macro Functions from Macro Variables. Impossible?

Astounding — Thu, 21 Jan 2016 17:01:42 GMT

Having read this question for a third time, I'm still stumped. Why is this code neat and easy to read:


        create table Test2 as
        select
        1 as A
        ,2 as B
        &AA. ,3 as C &BB.
        from Test
    ;quit;

Yet this code isn't?


        create table Test2 as
        select
        1 as A
        ,2 as B
        %AA ,3 as C %BB
        from Test
    ;quit;

Or even better yet:


        create table Test2 as
        select
        1 as A
        ,2 as B
        %Debt3m ,3 as C %latest_wod
        from Test
    ;quit;

What am I missing?

Re: Executing Macro Functions from Macro Variables. Impossible?

TomekO — Fri, 22 Jan 2016 00:26:15 GMT

So I could use %AA to have the If then code instead of &AA.?

Re: Executing Macro Functions from Macro Variables. Impossible?

TomekO — Fri, 22 Jan 2016 00:29:40 GMT

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.

Re: Executing Macro Functions from Macro Variables. Impossible?

Kurt_Bremser — Fri, 22 Jan 2016 07:19:22 GMT

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.