Problem extracting from table name w/parameter when using a macro

[ENM]

Hello, I have a problem I'm hoping you might help me with.

I need to extract data from a table called CLK1_PREMIUM_(Period_yyyymm). As you can see, it has a parameter in the name. An example of a physical table name is CLK1_PREMIUM_201407.

If I add the following line in the precode, I can extract the data just fine:

call symput('period_yyyymm', '201407');

However, I don't want to hardcode 201407. It must be automated. So, instead I use the below code as precode. It finds the previous month using the intnx function, and puts it into the correct format.

data _null_;

     prev_month_date = intnx('month', date(), -1, 'e');

     prev_month_yyyymm = compress(catx('', year(prev_month_date), put(month(prev_month_date), z2.)));

     call symput('period_yyyymm', prev_month_yyyymm);

run;

Despite the fact that period_yyyymm=201407 when I use this precode, just as it was when i hardcoded it, the extract fails. It tells me "ERROR: The value 'Calc1.CLK1_PREMIUM_201407'n is not a valid SAS name."

My problem is that SAS for some reason adds quotes and 'n' to the table name it tries to extract from (see red highlight above). I don't get why it does this when I use the second precode, but not when I use the first hardcoded precode.

I'm running this in DI Studio 4.7.

Do anyone here have insight into this problem?

Thanks.

[RW9]

Hi,

Can you post all your code as:

data _null_;

     prev_month_date = intnx('month',date(),-1,'same');

     prev_month_yyyymm = compress(catx('', year(prev_month_date), put(month(prev_month_date), z2.)));

     call symput('period_yyyymm', prev_month_yyyymm);

run;

%put &period_yyyymm.;

data want_&period_yyyymm.;

  set sashelp.cars;

run;

The above code works correctly creating a dataset want_201407.  Note to turn on:

options mlogic mprint symbolgen;

so you can see what is happening.

[ENM]

Thanks for the feedback. I've done as your suggested and reviewed the log, but can't see why this keeps happening. The parameter value is 201407 as before, and the extract still fails with the same error.

Here's the relevant parts of the log, perhaps you have keener insight than me:

http://codeviewer.org/view/code:42e6

Incidentally, the real table name is "CLK1_OPPTJENT_PREM_(Period_yymm)", I just adjusted it slightly for the example above.

And again, it all works perfectly if I use this precode instead:

call symput("period_yymm", '201407');

Bizarre. 😕

EDIT: For some reason, replying directly to RW9 kept causing this error: "An error occurred while trying to submit your post. Please try again.", so I had to reply to myself instead.

[RW9]

Where are you doing this?  I see you are using DI studio which I am not familiar with and you mention: "CLK1_OPPTJENT_PREM_(Period_yymm)",

A couple of times which isn't Base SAS syntax.  a dataset name does not have quotes around it, and the () bit should be replaced by &macro variable.  I suspect from looking at the warning message that it is treating the text as a literal.  This syntax of 'xyz'n comes up when accessing Excel files for instance, it shouldn't be happening with a dataset.

But as I say, I don't use DI so can't be of much help there.

[Astounding · Accepted solution]

One possibility:  Despite the COMPRESS function, DATA step logic will define PREV_MONTH_YYYYMM as longer than you might think.  It contains trailing blanks, and those trailing blanks are transferred to the macro variable by CALL SYMPUT.  Switch to CALL SYMPUTX which will automatically strip away leading and trailing blanks.

[ENM]

Thanks for the good advice, using call symputx did indeed solve the problem.