Date:         Tue, 25 Jan 2005 21:00:31 +0000
Reply-To:     iw1junk@COMCAST.NET
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ian Whitlock <iw1junk@COMCAST.NET>
Subject:      Re: return count in proc sql as macro variable
Comments: cc: toby dunn <tobydunn@HOTMAIL.COM>, joanab1970@YAHOO.COM

Toby,

Your SQL code is a reasonable simplification of Joan's problem,
but you went too far in suggesting that it could be wrapped in a macro
%ABORTCODE and used as

>  %if %abortcode(lib = alpha , mem = records) %then %do ;

You cannot generate SAS code in the middle of a macro decision.
For this line to work, the macro must generate nothing but the
code value, say via %SYSFUNC and functions to get the count.

If you are going to use the dictionary information then perhaps

    data _null_ ;
        call symput ( "abortflag" , put(not(nobs),1.) ) ;
        stop ;
        set records nobs = nobs ;
   run ;

is simplest.

Joan,

The issue is whether the count is reliably stored in either th dictionary or the data.
It isn't for views, datasets where records are marked deleted, and non-native datasets.  In these cases you must physically check that a record is present with SQL or a DATA step, since you do not care how big the count is.

    %let abortflag = 1 ;
    data _null_ ;
          set records ( obs = 1 ) ;
          call symput ( "abortflag" , "0" ) ;
     run ;

Ian Whitlock
=================
Date:         Tue, 25 Jan 2005 18:30:00 +0000
Reply-To:     toby dunn <tobydunn@HOTMAIL.COM>
Sender:       "SAS(r) Discussion"
From:         toby dunn <tobydunn@HOTMAIL.COM>
Subject:      Re: return count in proc sql as macro variable
Comments: To: joanab1970@YAHOO.COM
In-Reply-To:  <20050125174827.75591.qmail@web30903.mail.mud.yahoo.com>
Content-Type: text/plain; format=flowed
Joan,
Your making yoru problem way harder than nesseccary,
try the following:
proc sql noprint ;
select nobs into : abortflag
  from dictionary.tables
   where libname = upcase("your libname here") and memname =
upcase("records") ;
quit ;
which could be wrapped in a %macro wrapper and the macro variable abortflag
can be declared local, you will pass the library and memname to the macro.
It there by can be reused easily without fear of screwing something up
directly in your code for a more mainatnable and readable program:
EX.
%if %abortcode(lib = alpha , mem = records) %then %do ;
data update;
set records
      new_records ;
run ;
%end ;


Toby Dunn


From: Joan A <joanab1970@YAHOO.COM>
Reply-To: Joan A <joanab1970@YAHOO.COM>
To: SAS-L
Subject: return count in proc sql as macro variable
Date: Tue, 25 Jan 2005 09:48:27 -0800

Please help with the following:
I would like to return the count from proc sql as a macro variable, and use
that macro variable in my code.  Please see code below.
Your assistance is greatly appreciated!!
Thanks, Joan
proc sql;
create table Records as
  select count(*) as numrecords from Values
.....
.....
quit;
data _null_;
  set Records;
if &countrecords gt 0 then do;
call symput('abortFlag',"false");
end;
else if &countrecords=0 then do;
call symput('abortFlag',"true");
end;
run;
__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com