BookmarkSubscribeRSS Feed
TomekO
Calcite | Level 5

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. 

 

16 REPLIES 16
ad123123
Fluorite | Level 6

 

 

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
Calcite | Level 5

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
Super User

(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
Calcite | Level 5

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
Super User

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
Super User

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
Fluorite | Level 6

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
Diamond | Level 26 RW9
Diamond | Level 26

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
PROC Star

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
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5
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
Super User

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
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 16 replies
  • 1600 views
  • 3 likes
  • 6 in conversation