Help using Base SAS procedures

proc sql where based on external declared variable

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 127
Accepted Solution

proc sql where based on external declared variable

[ Edited ]

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,

SH

 


Accepted Solutions
Solution
‎10-21-2016 08:32 AM
Super User
Posts: 7,094

Re: proc sql where based on external declared variable

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Super User
Posts: 7,565

Re: proc sql where based on external declared variable

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

where PAID_YYYYMM='&date.' ;quit;

 

If

%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.

Frequent Contributor
Posts: 127

Re: proc sql where based on external declared variable

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. :-(

 

Super User
Posts: 18,542

Re: proc sql where based on external declared variable


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. 

Frequent Contributor
Posts: 127

Re: proc sql where based on external declared variable

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

Solution
‎10-21-2016 08:32 AM
Super User
Posts: 7,094

Re: proc sql where based on external declared variable

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 259 views
  • 2 likes
  • 4 in conversation