SQL select without from

[K507]

I’m trying to make SAS SQL query without FROM clause. For example in
Oracle it works as: SELECT “cat” as animal FROM DUAL (it will return one
row with one column (animal) with a “cat” as a value). Is there anything
similar in SAS SQL? Of course I can use any table, but I need to find
more elegant solution. I appreciate any help

K507

[Jack Hamilton]

The Oracle SQL you quotes does have a FROM clause. What you're
asking is "Is there an equivalent to the DUAL table in SAS?"

Unfortunately, there's not, but you can use one of the dictionary
tables, which will always be available.

Again unfortunately, I don't think there's a dictionary table that's
guaranteed to contain exactly one observation. You can fake it:

===== (untested code)
select "cat" as animal from dictionary.members where libname = 'WORK';
=====

I have found it convenient on occasion to create my own version of
DUAL, only slightly more useful. See my paper "Digits and Dates: PROC
SQL Goes Loopy", <http://www.excursive.com/sas/DigitsAndDates.pdf>.

--
Jack Hamilton

I’m trying to make SAS SQL query without FROM clause. For example in
Oracle it works as: SELECT “cat” as animal FROM DUAL (it will return
one
row with one column (animal) with a “cat” as a value). Is there
anything
similar in SAS SQL? Of course I can use any table, but I need to find
more elegant solution. I appreciate any help
K507

[Paulo Rodrigo]

You can try the following sintax:

proc sql inobs=1; /* limit read to only one observations */
select ranuni(0) as tot
from sashelp.table; /* or any table desired */
quit;

Att: Paulo R. Herculano
SAS Consultant
[phone]

I'm trying to make SAS SQL query without FROM clause. For example in
Oracle it works as: SELECT "cat" as animal FROM DUAL (it will return one
row with one column (animal) with a "cat" as a value). Is there anything
similar in SAS SQL? Of course I can use any table, but I need to find
more elegant solution. I appreciate any help
K507

[Ken Borowiak]

Iï¿œm trying to make SAS SQL query without FROM clause. For example in
Oracle it works as: SELECT ￜcatￜ as animal FROM DUAL (it will return one
row with one column (animal) with a ￜcatￜ as a value). Is there anything
similar in SAS SQL? Of course I can use any table, but I need to find
more elegant solution. I appreciate any help
K507

A DATA step is the most straight forward way to do this. But if you insist
on a SQL solution, then try:

proc sql ;
create table foo
( animal char(3) ) ;

insert into foo values ( "CAT" ) ;
quit ;

Regards,
Ken Borowiak

[unknown]

Post by Jack Hamilton
The Oracle SQL you quotes does have a FROM clause. What you're
asking is "Is there an equivalent to the DUAL table in SAS?"
Unfortunately, there's not, but you can use one of the dictionary
tables, which will always be available.
Again unfortunately, I don't think there's a dictionary table that's
===== (untested code)
select "cat" as animal from dictionary.members where libname = 'WORK';
=====

I think Jack meant dictionary.libnames

Post by Jack Hamilton
I have found it convenient on occasion to create my own version of
DUAL, only slightly more useful. See my paper "Digits and Dates: PROC
SQL Goes Loopy", <http://www.excursive.com/sas/DigitsAndDates.pdf>.
--
Jack Hamilton

Iï¿œm trying to make SAS SQL query without FROM clause. For example in
Oracle it works as: SELECT ￜcatￜ as animal FROM DUAL (it will return
one
row with one column (animal) with a ￜcatￜ as a value). Is there
anything
similar in SAS SQL? Of course I can use any table, but I need to find
more elegant solution. I appreciate any help
K507

[Wensui Liu]

are you looking for something similar to SET statement in sql to
assign a single value to a parameter? I don't think there is one alike
in proc sql.

I'm trying to make SAS SQL query without FROM clause. For example in
Oracle it works as: SELECT "cat" as animal FROM DUAL (it will return one
row with one column (animal) with a "cat" as a value). Is there anything
similar in SAS SQL? Of course I can use any table, but I need to find
more elegant solution. I appreciate any help
K507

--
===============================
WenSui Liu
Acquisition Risk, Chase
Email : ***@chase.com
Blog : statcompute.spaces.live.com
===============================

[Sigurd Hermansen]

Agent K507:
I suggested in earlier SAS-L postings that SAS include an appropriate
_NULL_ table in the SASHELP library/schema for this purpose. Until that
happens, I've substituted: FROM SASHELP.class (obs=1)....
S

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-***@LISTSERV.UGA.EDU] On Behalf Of
K507
Sent: Sunday, July 27, 2008 5:11 PM
To: SAS-***@LISTSERV.UGA.EDU
Subject: SQL select without from


I'm trying to make SAS SQL query without FROM clause. For example in
Oracle it works as: SELECT "cat" as animal FROM DUAL (it will return one
row with one column (animal) with a "cat" as a value). Is there anything
similar in SAS SQL? Of course I can use any table, but I need to find
more elegant solution. I appreciate any help

K507

[i***@gmail.com]

Post by K507
I’m trying to make SAS SQL query without FROM clause. For example in
Oracle it works as: SELECT “cat” as animal FROM DUAL (it will return one
row with one column (animal) with a “cat” as a value). Is there anything
similar in SAS SQL? Of course I can use any table, but I need to find
more elegant solution. I appreciate any help
K507

As past Oracle PL/SQL programmer, using DUAL is handy as a simple calculator or a simple scenario/sql tester. As mentioned by Jack Hamilton SELECT “cat” as animal FROM DUAL does have a FROM. However, the original poster intended to state that the "result" doesn't come from a table.

In a default SAS installation with local tables, the following are possible to mimic what DUAL allows in Oracle SQL:

proc sql;

select 1 from sashelp.class (obs=1);

select 1 as number from sashelp.class (obs=1);

select '1' format=$12. from sashelp.class (obs=1);

select '1' as charx format=$12. from sashelp.class (obs=1);

select 9/(5*1024) from sashelp.class (obs=1);

select 9/(5*1024) format=dollar10.6 from sashelp.class (obs=1);

select put(9/(5*1024),dollar10.6) from sashelp.class (obs=1);

select 'cat' from sashelp.class (obs=1);;

select 'cat' as animal from sashelp.class (obs=1);

quit;

The "Dictionary" library is not visible, so you have to know the names of the tables before using them as a replacement for DUAL. So, for anyone who is a "noob" to SAS (a nod to 'noobs' in SAS, lol), any table in sashelp (such as sashelp.class is a good replacement for DUAL). The obs=1 in parentheses is required to ensure the select clause is only executed once. Omitting it will mean that the clause will execute once for each row within the table being used.

Creating a table called DUAL in the default work library is an alternative (whether by using SQL or by using SAS data step [the latter is easier than SQL]) but you need to ensure that any pseudo column name used within the select clause you try out does not match the column name used to create the DUAL.
e.g.
DATA DUAL;
_dummy_ = " ";
RUN;
(run this once per SAS session - column name is _dummy_ which is unlikely to be needed 'in anger' and the value is a SAS null [for a character column]).

Then you can use:

proc sql;
select 1 from DUAL;
quit;
(or whatever you need).

However, once your session is closed the DUAL table will be deleted, so you have to re-create it for every session in which you intend to use the DUAL.

So, if you have 'write' permissions to another library then you could create it there (so that it doesn't need to be re-created every time). But this means that DUAL would need to be prefixed by the relevant library name each time.

e.g.
DATA MYLIB.DUAL;
_dummy_ = " ";
RUN;

then in any future session you can use :

proc sql;
select 1 from MYLIB.DUAL;
quit;

until MYLIB.DUAL is deleted.