topic Re: proc sql where based on external declared variable in SAS Procedures

proc sql where based on external declared variable

Sir_Highbury — Fri, 21 Oct 2016 10:00:13 GMT

Dear experts,

I have the following variable: PAID_YYYYMM (length 25, format $25., informat $25.)

I would like to filter the table using the following variable: %let date=201609;

To do it I am using the proc sql, stating the where as: PAID_YYYYMM='201609' but replacing the number with the variable date.

I tried the following matching but I get always an empty data set:

where PAID_YYYYMM='&date.' ;quit;
/* where PUT(PAID_YYYYMM, $10.)="'&date.'" ;quit; */
/* where PUT(PAID_YYYYMM, $10.)='&date.' ;quit; */

What is wrong in the logic used?

Thanks in advance for the support.

Best regards,

Re: proc sql where based on external declared variable

RW9 — Fri, 21 Oct 2016 11:26:59 GMT

Well, take a look at what is generated from the macro:

where PAID_YYYYMM='&date.' ;quit;

%let date='201609';

Then:

where PAID_YYYYMM='201609';

Are there recrod in your read in dataset where paid_yyyymm='201609'?

Its very hard to tell you what is wrong with the logic without seeing the data it is operating on - post some test data (in the form of a datastep) will make it easier to debug.

Re: proc sql where based on external declared variable

Kurt_Bremser — Fri, 21 Oct 2016 12:21:08 GMT

If you really used single quotes around the macro variable, why are you surprised?

Single quotes prevent the resolution of macro triggers. Use double quotes. Only double quotes.

Re: proc sql where based on external declared variable

Sir_Highbury — Fri, 21 Oct 2016 12:29:02 GMT

Dear RW9,

thanks for your support, the data is specified as it follows: %let date=201609 and I cannot change it (e.g. '201609'). I cannot share the data set I am working on. 😞

Re: proc sql where based on external declared variable

Reeza — Fri, 21 Oct 2016 20:34:24 GMT

@Sir_Highbury wrote:

I cannot share the data set I am working on. 😞

You don't need to share the data set you're working on. You need to provide sample data, which can be fake, that is enough to replicate your problem. It just needs to be similar enough so we can understand your issue. You'll get better and faster answers. if you do this.

Re: proc sql where based on external declared variable

Sir_Highbury — Mon, 24 Oct 2016 09:26:23 GMT

It was a clear question and I got the issue solved without losing time to create the fake error. Thanks for your effort but please try to answer if you can. The decision about the question and if and how attach data is mine, if you disagree feel free to ignore the topic. 😉

Have a nice day, SH