topic Deriving Macro variable name from other macro variables in SAS Programming

Deriving Macro variable name from other macro variables

venkata_peddisetty — Mon, 22 Sep 2014 11:55:35 GMT

Hi,

I am creating a macro for create table in teradata and i woudl like to call that macro for each table. So i have wrote the below mentioned code but i am getting a typical warning and the table is not created as below

Code:

%let ini= VK;

%let fcode=ab123;

%let work=wt;

%let brand2 = Very;

%let SEASON2=AW14;

%macro filecheck (DB=, Tablename=, sql_code=, indx=);

proc sql;

connect to teradata (user=&user. password=&password. mode=teradata);

OPTIONS COMPRESS=YES;

EXECUTE (ct &tablename. &sql_code. &indx.) by teradata;

%mend;

%filecheck_create
(DB=*****, Tablename=%put(%trim(&ini._&Fcode._brow_&brand2._&season2.)_&work.),

sql_code=(aba, abc), indx= primary index (abc)) by teradata;

WARNING: The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation marks.

Above code resolving the table name but it is not creating the table and generating the warning. Can some one help what is the solution for the same to define table in the same pattern.

Regards,

Venkat P

Re: Deriving Macro variable name from other macro variables

RW9 — Mon, 22 Sep 2014 12:45:33 GMT

Hi,

I suspect that the this is the problem:

%put(%trim(&ini._&Fcode._brow_&brand2._&season2.)_&work.)

Not sure why you need the %put to start with. Also the %trim is great, however your not trimming the individual elements:

Use options mlogic mprint symblogen;

You will see that &ini for instance will resolve as the text, plus some spaces (up to minimum length 8 I believe)so your output may look like:

ini=xyxy, bran2=tytyty

xyxy _brow_tytyty ...

Try to trim each bit:

%trim(&ini)_%trim(&fcode)_brow_%trim(&brand2)...

AS a final thought, you might want to consider using catx function e.g:

%sysfunc(catx("_",&ini.,&fcode.,"_brow_",&brand2....)

Re: Deriving Macro variable name from other macro variables

jakarman — Mon, 22 Sep 2014 13:09:38 GMT

You are defining a macro named filecheck and than using one filecheck_create. Getting an error that is most likely caused by unbalanced apostrophes.

The things you have posted is inconsistent. Restart your eguide/sas-session and before submitting any code, review that carefully on colors indicating something coded badly.

Re: Deriving Macro variable name from other macro variables

venkata_peddisetty — Mon, 22 Sep 2014 13:33:52 GMT

Hi RW / Jaap,

Well the issue is resolved and thanks for you both for quick reply.

Sorry Jaap while giving example i hgave incorrect Macroname, but in the process i used corret name. The issue with trim function using for all the macro variables, thanks RW.

Regards,

Venkat P

Re: Deriving Macro variable name from other macro variables

Tom — Mon, 22 Sep 2014 13:39:02 GMT

From your error message it looks like you have unbalanced quotes somewhere in your program.

As to the idea of building up macro variable names (or any other string) from macro variables you just need to reference the variables in the right place. For example if you want to build the value for the Tablename parameter from all of those other macro variables then just reference them in order. Tablename=&ini._&Fcode._brow_&brand2._&season2._&work.

If that does not work (say because it is producing spaces in the middle of what should be a table name) then the problem is in the individual macro variables and should be fixed when they are created.

If what you wanted to build was not a table name, but instead the name of a macro variable that would actually hold the name of the table then you could add two extra &'s in front. When SAS sees two adjacent &'s it resolves them to one & and then re-evaluates the results again for macro variable references.