Date: Mon, 24 Aug 2009 13:45:46 -0700
Reply-To: Jack Hamilton <jfh@STANFORDALUMNI.ORG>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Jack Hamilton <jfh@STANFORDALUMNI.ORG>
Subject: Re: PROC SQL Built-In Dummy Table
In-Reply-To: <0E9A1D6663A84A6A9C03EDD0C3BBC7F0@KAM1720>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed; delsp=yes
There's not one that I know of, but there are a SASHELP tables that
will always exist.
I have a standard table I create when I need something like that. I
call it Digits, and it has 10 entries with values from 0 to 9. This
lets me create "loops" in SQL, in addition to doing things like you
suggest.
<www2.sas.com/proceedings/sugi31/046-31.pdf>. There was also some
discussion on SAS-L in which various contributors added interesting
information.
--
Jack Hamilton
jfh@alumni.stanford.org
Tots units fem força!
On Aug 24, 2009, at 1:22 pm, Kevin Myers wrote:
> In a PROC SQL step that is doing a bunch of other stuff as part of a
> macro, I would like to populate a macro variable using based purely
> on the value of an expression, where that expression is purely based
> on numbers (some of those numbers come from macro variables) rather
> than from columns of any existing table. It should look something
> like the following:
>
> proc sql noprint;
> select mycomplexexpressionhere into :myvar from _NULL_;
> quit;
>
> The reason that I want to do this is that I am already in a PROC SQL
> step when this computation is needed, and due to efficieny issues
> (this is in a macro loop that gets executed many times), I don't
> want to jump out of SQL, into a data step, and back. I could
> theoretically evaluate the expression purely in macro code by using
> a combination of %sysevalf and %sysfunc. But I don't want to do
> that because the expression is very complex, uses a lot of
> functions, and would get ridiculously ugly if I had to add all of
> the %sysfunc() calls necessary to make it work properly.
>
> Now the statement listed above won't work as is, and causes SQL to
> generate the follow error message:
> ERROR: Table WORK._NULL_ doesn't have any columns. PROC SQL requires
> each of its tables to have at least 1 column.
>
> Other database systems have other dummy table names or keywords that
> allow one to accomplish the same thing, e.g.:
>
> select myexpression from DUAL; /* Oracle */
>
> I could create a dummy table with a single row just to facilitate
> this query (and I could even name it DUAL just for the sake of
> consistency with Oracle), or I could refer to some other real table
> or dictionary entity using (obs=1), but either of those approaches
> would just seem a bit klutzy.
>
> I could swear that there is a built-in dummy table-like entity
> already available in PROC SQL that can be specifically used for
> oddball stuff like this. I think that it exists either because SQL
> uses it internally, or maybe it is used for testing. In any case,
> I'm pretty sure it is there, but I don't remember what it is
> called. Do you know what I am talking about and what it is called?
>
> Thanks,
> Kevin M.
| 
