topic No rows beng generated in SAS Enterprise Guide

No rows beng generated

Rsadiq — Thu, 20 Apr 2017 13:40:11 GMT

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

Re: No rows beng generated

CaseySmith — Thu, 20 Apr 2017 14:14:44 GMT

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

Re: No rows beng generated

RW9 — Thu, 20 Apr 2017 13:55:59 GMT

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.

Re: No rows beng generated

Rsadiq — Thu, 20 Apr 2017 14:03:12 GMT

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

Re: No rows beng generated

Rsadiq — Thu, 20 Apr 2017 14:04:24 GMT

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

thanks

Rida

Re: No rows beng generated

Tom — Thu, 20 Apr 2017 14:33:21 GMT

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.