How to use macro variable with derived value in PROC SQL

Question

I have a situation where I need to assign a table name with a macro variable whose value is derived from another macro variable.

However, the generated code in macro uses the derivation formula rather than the derived value itself.

    %macro testing;

    %let n=2;
    %let k=&n-1;
    %let opp=&k;

    PROC SQL;

    CREATE TABLE WORK.TABLE_&opp AS
    SELECT * from WORK.SOURCETABLE;

    QUIT;

    %mend;

    %testing

This resulted in error as the code generated

NOTE: Line generated by the macro variable "OPP".
34          WORK.TABLE_2-1
                    _
                    22
                    200
ERROR 22-322: Syntax error,

But I am expecting the resultant table will be named as WORK.TABLE_1 instead of WORK.TABLE_2-1.

So how can I force SAS to identify variable opp=1 rather than opp=2-1?

Thank you

support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/… - you can read up about %eval here — Nirvik Banerjee
– Nirvik Banerjee, Commented Jul 4, 2016 at 9:14

Nirvik Banerjee · Accepted Answer · 2016-07-04 09:13:45Z

2

Try using %eval as shown under:

 %macro testing;

    %let n=2;
    %let k=&n-1;
    %let opp=%eval(&k);

    PROC SQL;

    CREATE TABLE WORK.TABLE_&opp AS
    SELECT * from WORK.SOURCETABLE;

    QUIT;

    %mend;

    %testing

answered Jul 4, 2016 at 9:13

Nirvik Banerjee

3355 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

buttermilkmarinade Over a year ago

Yep, seems to do the trick. Need to read up more on macro functions. Thanks!

Longfish Over a year ago

A macro variable is stored as a text string, so SAS doesn't recognise any calculation in a %let statement, unless a macro function is used. I'm not sure why you have variables k and opp, as they take the same value (unless this is purely for example purposes). You could put %let k=%eval(&n-1) to get the same answer

gowerc Over a year ago

Just note that you need SYSEVALF if you are not performing integer operations

Collectives™ on Stack Overflow

How to use macro variable with derived value in PROC SQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related