Date: Thu, 10 Aug 2000 14:03:42 -0700
Reply-To: Laurie Fleming <lflemingNOlfSPAM@ACTRIX.GEN.NZ.INVALID>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Laurie Fleming <lflemingNOlfSPAM@ACTRIX.GEN.NZ.INVALID>
Organization: http://www.remarq.com: The World's Usenet/Discussions Start Here
Subject: Re: Number of observations in a table of a Oracle Library
Here's my macro (with accompanying error display macro putmsg)
which will dynamically generate the oracle (or sybase) query to
find the number of observations.
To use:
Create a view of the Oracle table.
(proc sql;
create view oracle.table_view as
select *
%countobs(dsn=oracle.table_view, oracle=y)
%put &_nobs;
Macro variable _nobs contains the number of rows in the table.
If you omit the oracle parameter, the macro does a SAS count of
the view - hugely inefficient, but it does work.
The weight parameter multiplies the number of observations by
the value in the variable/column.
Obviously this works on any SAS data object - SAS view, dataset,
xport - as well, but is here optimised for Sybase and Oracle
(which have subtle differences in their syntax).
Any where clause stuff you put in will be passed directly to the
Oracle query.
%macro countobs(dsn=, sybase=n, oracle=n, where=, weight=,
debug=n)
/ des='Get number of obs in a dataset or view';
/*
Macro name: countobs
Source: countobs.sas under
~flemingl
Author: Laurie Fleming,
Sysware Consulting Group Ltd (laurie@sysware.co.nz)
Date written: 12 February 1997
Purpose: Count observations
in a SAS dataset or view.
Parameters: dsn - dataset name.
sybase - Generate
sybase query to count rows
oracle - Generate
oracle query to count rows
where - Where clause
weight - Weight the
result by the column/variable name
Macros required: putmsg - display
error messages
Method:
%global/%local/%let Define variables and
initialise them.
%if &dsn... Parse parameters
%if &sybase = Y and %eval... If it's a Sybase
table and the dsn is a view,
get the connection
information from the view
description. From
this, generate an sql
connection and get
Sybase to count the number
of rows. This could
be a security issue, if the
table is owned by
somebody else.
%if %sysfunc(exist... Check existence and
member type.
attrn(&dsn, nlobs) Get the number of
logical observations,
if available. This
is the total observations
minus the deleted
observations. If not available,
data _null_ physically read and
count observations.
%EndMac That's the end...
Modification history (reverse
chronological order)
Laurie Fleming, 12 April 2000 Added Oracle
funtionality.
Laurie Fleming, 26 April 1999 Allowed for
sequential-engined XPORT datasets to be counted.
Laurie Fleming, 11 March 1998 Changed counter from
'count' to '_count' - this reduces
the chances of
undefined results because of the source
data having a
variable used by the 'data _null_'.
Laurie Fleming, 24 August 1997 Allowed 'where'
processing to be done on a Sybase view.
Laurie Fleming, 13 August 1997 Added new parameter
'where', to subset the dataset.
Laurie Fleming, 4 July 1997 Changed name of
global variable __error to __cnterr,
to avoid conflicting
with __error in sybload.
Laurie Fleming, 9 May 1997 Changed reset of
'proc printto' log to standard log.
When SAS runs in
background this doesn't work very
well otherwise (with
just 'proc printto'). Corrected
bug in scanning of
Sybase view where the 'from ' and
Sybase table name
are split over two lines. SAS was
trying to read past
the end of the variable.
2 May 1997 Included Sybase row
counting - faster than SAS's
counting of rows in
a Sybase view by several orders
of magnitude.
April 1997 First written.
*/
/*
Declare global and local variables.
*/
%global _nobs _dbase _table __cnterr;
%let __cnterr = 1;
run;
quit;
%local notes mprint symblgen mlogic ls source2 fn saverc memtype
engine libname dbms;
/*
Get option information.
*/
%let mprint = %sysfunc(getoption(mprint));
%let notes = %sysfunc(getoption(notes));
%let symblgen = %sysfunc(getoption(symbolgen));
%let mlogic = %sysfunc(getoption(mlogic));
%let source2 = %sysfunc(getoption(source2));
%let ls = %sysfunc(getoption(linesize));
/*
Parse parameters.
*/
%if &dsn = %then %do;
%putmsg(msg=DSN is a compulsory parameter.);
%let _nobs = ERROR;
%goto EndMac;
%end;
%if &sybase =
%then %let sybase = N;
%else %let sybase = %upcase(%substr(&sybase, 1, 1));
%if &oracle =
%then %let oracle = N;
%else %let oracle = %upcase(%substr(&oracle, 1, 1));
%if &debug =
%then %let debug = N;
%else %let debug = %upcase(%substr(&debug, 1, 1));
%if &debug = N
%then %str(option nomprint nonotes nosymbolgen nomlogic
nosource2;);
%else %str(option mprint symbolgen mlogic notes source2;);
%let where = %quote(%unquote(&where));
%let weight = %quote(%unquote(&weight));
%if %eval(%index(&dsn, .) = 0)
%then %let libname = work;
%else %do;
%let libname = %scan(&dsn, 1, .);
%let dsn = %scan(&dsn, 2, .);
%end;
/*
Initialise number of observations, in case no number can
be found.
*/
%let _nobs = .;
/*
Check if dataset or view exists, and what memtype (data or
view) it is.
*/
proc sql noprint;
select memtype, engine
into :memtype, :engine
from dictionary.members
where libname = "%upcase(&libname)" and memname = "%upcase
(&dsn)";
quit;
%if &memtype = %then %do;
%putmsg(msg=Dataset &libname..&dsn does not exist.);
%let _nobs = ERROR;
%goto EndMac;
%end;
%if (&sybase = Y or &oracle = Y) and &memtype = VIEW %then %do;
%if &sybase = Y
%then %let dbms = sybase;
%else %let dbms = oracle;
/*
Set up file references to contain log from view
description,
and code generated from examining the description.
*/
filename descview catalog 'work.countobs.descview.log';
filename gencount catalog 'work.countobs.gencount.source';
/*
Change linesize to 200, to make scanning of log more
reliable (it works better, and faster,
with long record lengths).
*/
option ls=200;
/*
Move log to descview (see above), instead of the log
window.
*/
proc printto log=descview new;
/*
Put description of view into the log. Part of what it
contains is the connection
information, including Sybase user id and password (!).
This could be a security
issue.
*/
proc sql;
describe view &libname..&dsn;
quit;
/*
Reset the log and put the linesize back to what it was.
*/
proc printto log=log;
run;
option ls=&ls;
/*
Read the log and from it pick out the salient data,
reformatting it and
generating the code to count the number of rows in the
Sybase/Oracle table referenced
by the SAS view.
*/
data _null_;
infile descview flowover n=2 column=colnum line=linenum
ls=200 length=linelen;
file gencount;
retain m1 -1 spltline 0;
length connect record $ 200 database tablenme $ 30;
/*
Read through the log until the string 'options=' is
found. Note the use of
'#(linenum - 1)'. I don't know why this is required (it
isn't later when much
the same input style is used), but this is the only way
I could get it to work.
Oddly the input, despite the trailing '@', still goes
onto the next line and
it's necessary to go backwards a line (with a bit of
help from the 'n=2' on the
infile).
*/
input @'options=' @;
index1 = colnum; /* Save the column
position. */
input #(linenum - 1)
@1 connect $varying200. linelen;
index2 = indexc(connect, ')') - index1 + 1; /* Get the
length of the connection string. */
%if &sybase = Y %then %do;
database = substr(connect, index(connect, 'database="') +
10, 30);
call symput('_dbase', scan(database, 1, '"'));
%end;
connect = substr(connect, index1, index2) || ';';
/*
Start generating the SAS SQL code to count the rows.
*/
put 'proc sql noprint;'
/ "connect to &dbms"
connect
/ 'select count'
/ ' into :_nobs'
/ " from connection to &dbms";
/*
Scan further through the log to find the string 'from ';
the word after this string
is the name of the Sybase table.
Save the column position, because it will be overwritten
with the next input.
*/
input @'from ' @;
index1 = colnum;
/*
It's possible for the 'from' and the Sybase table name
to be split over two lines. If
they are, the column position that the pointer is set at
is greater than the line
length, so this has to be flagged and the current line
to be finished with.
*/
if index1 ge linelen then do;
spltline = 1;
input;
end;
input @1 record $varying200. linelen;
if spltline then /* Find the first non-
space, non-parenthetical character. */
index1 = verify(record, ' )(');
/*
Pick up the tablename from the record.
*/
tablenme = scan(substr(record, index1), 1, ' )(');
call symput('_table', trim(tablenme));
%if &weight =
%then %do;
%if &sybase = Y
%then %do;
put @4 "(select count(*) as 'count' "
%end;
%else %do;
put @4 "(select count(*) as count "
%end;
%end;
%else %do;
%if &sybase = Y
%then %do;
put @4 "(select sum(&weight) as 'count' "
%end;
%else %do;
put @4 "(select sum(&weight) as count "
%end;
%end;
/ @7 'from ' tablenme +m1 @;
%if &where ne %then %do;
put / @6 "where &where" @;
%end;
put ');'
/ "disconnect from &dbms;"
/ 'quit;';
stop;
run;
/*
Now that the code has been generated and stored in the
catalog(ue),
include (execute) it. The result (count) is placed in
the global
macro variable _nobs.
*/
%inc gencount;
%let saverc = &sqlrc;
/*
Purge the catalog(ue) which contains the log and source.
*/
filename descview clear;
filename gencount clear;
%if &debug = N %then %do;
proc datasets lib=work nolist nowarn mt=catalog;
delete countobs;
quit;
%end;
/*
Drop out...
*/
%goto Success;
%end;
/*
If it's a dataset, get number of total and deleted
observations, if available.
*/
%if &engine ne XPORT and &memtype = DATA %then %do;
%let fn = %sysfunc(open(&libname..&dsn, i)); /*
Create an open tag to the dataset/view */
%if %eval(&fn = 0) %then %do;
%putmsg(msg=Cannot open dataset/view &libname..&dsn..);
%let _nobs = ERROR;
%goto EndMac;
%end;
%let _nobs = %sysfunc(attrn(&fn, nlobs)); /* Get the
number of logical observations */
%let fn = %sysfunc(close(&fn)); /* Close the
dataset/view */
%end;
/*
If the result is '-1' the number of logical observations
cannot be ascertained,
so a data step (as opposed to an SQL query - that'd be
slightly less efficient)
is generated to count the rows.
*/
%if &engine = XPORT or &memtype = VIEW or %eval(&_nobs = -1) or
&_nobs = . or &where ne or &weight ne %then %do;
%if &weight = %then
%let weight = 1;
%let _nobs = 0;
data _null_;
set &libname..&dsn
%if &where = and &weight = 1 %then %do;
(drop=_all_)
%end;
end=eof;
%if &where ne %then %do;
where &where;
%end;
retain _count 0;
_count + &weight;
if eof then
call symput('_nobs', left(put(_count, best15.)));
run;
%end;
%Success:
%let __cnterr = 0;
%EndMac:
option &source2 ¬es &symblgen &mlogic &mprint;
%mend countobs;
%macro putmsg(msg=, type=ERROR) / des='Display macro error
messages in the log';
%if &type =
%then %let type = ERROR;
%else %let type = %upcase(&type);
%put;
%let msg = %quote(&msg);
%if &msg =
%then %put &type: (No further information);
%else %put &type: &msg;
%mend putmsg;
-----------------------------------------------------------
Got questions? Get answers over the phone at Keen.com.
Up to 100 minutes free!
http://www.keen.com
