return count in proc sql as macro variable

[Joan A]

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

[Dennis Diskin]

Joan,

proc sql;
select count(*) into :countrecords from Values;
quit;

HTH,
Dennis Diskin

Joan A <***@YAHOO.COM> wrote:
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

[j***@hotmail.com]

Joan,


You will get macro variables numrecords and abortFlag after running the
following sql.


proc sql;
select count(*) as numrecords
, case when (calculated numrecords) > 0 then 'false' else 'true'
end as abortFlag
into :numrecords
,:abortFlag
from values;
quit;



Hope that helps!

Juan

Post by Joan A
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.

Post by Joan A
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

[toby dunn]

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 <***@YAHOO.COM>
Reply-To: Joan A <***@YAHOO.COM>
To: SAS-***@LISTSERV.UGA.EDU
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

[Pardee, Roy]

Or, use the automatic variable &SQLOBS, which holds the # of records
affected by the last successfully run sql statement:

create table types as
select distinct type
from all_types ;

%let NumTypes = &SQLOBS ;

HTH,

-Roy

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-***@LISTSERV.UGA.EDU] On Behalf Of
Dennis Diskin
Sent: Tuesday, January 25, 2005 10:08 AM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: return count in proc sql as macro variable


Joan,

proc sql;
select count(*) into :countrecords from Values;
quit;

HTH,
Dennis Diskin

Joan A <***@YAHOO.COM> wrote:
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

[Richard A. DeVenezia]

Post by Joan A
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.

When you want the number of rows in table created by SQL, use the automatic
macro variable &SQLOBS.

proc sql;
create table foo as select * from sashelp.class;
%put sqlobs=&sqlobs;
quit;

When you want to count the number of rows in an existing table, use an INTO
clause

proc sql;
reset noprint;
select count(*) into :myMacroVar from sashelp.class;
quit;

%put myMacroVar=&myMacroVar.;

[Ian Whitlock]

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

Post by toby dunn
%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 <***@HOTMAIL.COM>
Sender: "SAS(r) Discussion"
From: toby dunn <***@HOTMAIL.COM>
Subject: Re: return count in proc sql as macro variable
Comments: To: ***@YAHOO.COM
In-Reply-To: <***@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 <***@YAHOO.COM>
Reply-To: Joan A <***@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

[Richard A. DeVenezia]

Post by Ian Whitlock
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.

...

Post by Ian Whitlock
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.

ATTRN() provides several perspectives on the 'number of observations'. In
particular, attribute NLOBSF (a variant of NLOBS) forces a pass through the
data to count the number of rows (while honoring options FIRSTOBS, OBS and
active WHERE clauses) obviating the need to cross a step boundary.

[toby dunn]

OOOOOo fidel sticks, your right Ian I jumped the gun and forgot that the
views wont be ready at macro compile and execute time, much less the fact
that the macro processor wont like having to run the code. Hmmm... I guess
I missed the boat on that end trying to create an easy tool for dicision
making. I really was attempting not to have to run through the data in a
data step and still have a good flag that either tripped teh rest of the
code to run or not to run.

Thanks Ian.

Toby Dunn




From: Ian Whitlock <***@COMCAST.NET>
Reply-To: ***@COMCAST.NET
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: return count in proc sql as macro variable
Date: Tue, 25 Jan 2005 21:00:31 +0000

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

Post by toby dunn
%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 <***@HOTMAIL.COM>
Sender: "SAS(r) Discussion"
From: toby dunn <***@HOTMAIL.COM>
Subject: Re: return count in proc sql as macro variable
Comments: To: ***@YAHOO.COM
In-Reply-To: <***@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 <***@YAHOO.COM>
Reply-To: Joan A <***@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