No rows beng generated

[Rsadiq]

Hello

I am running my code and I have datasets which contain rows and observations but for some reason I keep getting 0 rows being generated, no errors in the code just no output either, (below). What is causing this maybe. I am new to advanced SAS so I'm trying to figure it out

Thanks

Rida

12 The SAS System                                                                                     08:05 Thursday, April 20, 2017

409         CREATE TABLE rptlib.sum_responses_curryr AS
410         SELECT *
411         FROM outlib.sum_responses_all_yrly
412        where substr(put(RESP_YR,best.),1,4)='&begyr.'
413        ;
NOTE: Table RPTLIB.SUM_RESPONSES_CURRYR created, with 0 rows and 6 columns.

413      !  QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.05 seconds
      cpu time            0.01 seconds
     

[CaseySmith]

Hi Rida,

Your macro variable will not resolve since it is in single-quotes ('&begyr.').  Change those to double quotes and the macro variable will resolve.

EDIT: RW9 suggested a better way.  I just hastily noticed and replied that having a macro variable in single-quotes is not going to resolve.

Casey

[Rsadiq]

okay, I seemed to try that but it still says the same message

[RW9 · Accepted solution]

Hi,

You have several other questions on here, which are more or less the same.  Please refer to the answers there, mark one of the answers as correct.  They all come down to one basic thing, your trying to complicate matters by using macros, when you don't understand them or how Base SAS is working.  

This:

where substr(put(RESP_YR,best.),1,4)='&begyr.'

Can be replaced with:

where RESP_YR=&begyr.

[Rsadiq]

I do understand base SAS I just do not know what is causing this result, I will try it out

thanks

Rida

[Tom]

Let's walk an example value through this comparison to see why you are not getting any hits.

where substr(put(RESP_YR,best.),1,4)='&begyr.'

So if you had set the macro variable BEGYR to 2013

%let begyr=2013;

And the data had the variable RESP_YR set to 2013.

RESP_YR=2013;

First let's evaluate the left hand side. Working out from the inner most function call.

put(RESP_YR,best.)

Will convert the number 2013 into the string '        2013' since the default length for BEST format is 12. So then this 

substr(put(RESP_YR,best.),1,4)

will yield a string that has four blanks.

Looking at the right had side you have the literal string 

'&begyr.'

Since you used single quotes the macro reference is not expanded so you have a string that is 7 characters long and starts with '&' and ends with '.'.

So unless your RESP_YR has values like 201,300,000,000 the left hand side will never make somthing that looks lke a four digit string that might mean a year.  And even if it did it would never look like the right hand side since that is just letters.