X-Mailer:     Pegasus Mail v2.3 (R3).
Newsgroups: bit.listserv.sas-l
Date:         Thu, 3 Sep 1992 14:55:42 EDT
Reply-To:     Ray Pass <PASSRAY@CPMAIL-NZ.CIS.COLUMBIA.EDU>
From:         Ray Pass <PASSRAY@CPMAIL-NZ.CIS.COLUMBIA.EDU>
Subject:      non-dups, and a REPORT tidbit

At the risk of getting caught up in the Leicester Tornado, here goes.

Yesterday I sent out a request for an elegant SQL solution to a simple
problem.  I needed to compare two 50,000+ obs files which I knew had many
dups, and I needed to display the non-dups from each file.  As I said, I knew
how to do it with non-SQL DATA step techniques, and I could also crank it
out with a few SQL sub-queries.  I was hoping for a Howard/Andy/Kernon/whoever
answer incorporating a two liner using UNION or EXCEPT or JOIN or some other
SQL fancy fingerwork.  Alas, it didn't come.  The SQL answer, as provided by
Andy Norton and Jim Clark (and appended to by Kernon Gibes), was indeed the
one I was already writing, using two separate sub-queries.  It worked fine
and I got what I needed.  Joe Schunk suggested a use of EXCEPT, but it wasn't
quite what I needed.  Sanjay K. Pandey suggested using COMPARE.  I must admit
I have never used it before, but I'll check it out.  And that brings us to
Pete Rikard.  He suggested the non-SQL approach using MERGE.  That was the
"other" way I knew how to do it and I originally figured that SQL had to have
a better way, and therefore my original query.  So I decided to code it up
both ways for the hell of it.  Well the SQL approach took 1 min 44 sec and
the "other" way took 18 sec (6.07, MVS 9021-500).  So what's the moral here?
There isn't any.  I intend to learn more and more SQL because its a good
thing, but sometimes you just gotta stick to what you know best and you may
be doing yourself a favor in the end.

And now for the REPORT tidbit.  I may have sent this out before - I can't
remember.  The thrill of the SAS Comm thing is making me dizzy.  REPORT
doesn't have a built-in obs number so you have to create one.  You'd think it
would be easy enough to just COMPUTE a counter and increment it for each obs.
Something like:

                PROC REPORT other stuff;
                   COLUMNS OBS other stuff;
                   DEFINE OBS / COMPUTED;
                      other
                      stuff
                   COMPUTE OBS;
                      OBS+1;
                   ENDCOMP;

The problem here is that REPORT initializes each "report variable" in the
COLUMNS statement to 0 or blank before giving it a real value and then
printing it.  In this case, OBS would always be set to 0 and then incremented
to 1, and each line would have a 1 under the OBS column.  The trick is to use
another "DATA step variable" in the COMPUTE code segment, one that **doesn't**
appear in the COLUMNS statement.  This one gets initialized once only, and
**won't** get re-initialized for each output line.  This re-initializing only
happens to variables that are to be "printed", those that appear in the
COLUMNS statement.  The following will give you a computed var, OBS, that will
increment with each output line:

                PROC REPORT other stuff;
                   COLUMNS OBS other stuff, but not N;
                   DEFINE OBS / COMPUTED;
                      other
                      stuff
                   COMPUTE OBS;
                      N+1;
                      OBS=N;
                   ENDCOMP;

This is documented in a round-about manner in the REPORT manual on page 115.
I must admit that there are some quirks with this method, but it works in
simple REPORT programs pretty reliably.

So, thanks for the SQL attempts, and I hope the REPORT tip can help someone.
(And I hope you only get to see this once, even in Merry Old Leicester)

Ray


 *--------------------------------------------------------------------------*
 | Ray Pass, Ph.D.                                    Voice: (212) 305-7748 |
 | Columbia-Presbyterian Medical Center                 Fax: (212) 305-3302 |
 | 161 Ft. Washington Ave.       AP1310                                     |
 | New York, NY 10032-3784               passray@cpmail-nz.cis.columbia.edu |
 *--------------------------------------------------------------------------*
Comments: Gated by NETNEWS@AUVM.AMERICAN.EDU