Discussion:
SQL select without from
(too old to reply)
K507
2008-07-27 21:11:21 UTC
Permalink
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
2008-07-27 21:26:50 UTC
Permalink
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
2008-07-27 21:23:09 UTC
Permalink
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
+55-11-9226 6618
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
2008-07-27 21:29:57 UTC
Permalink
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
Howard Schreier)
2008-07-27 22:36:10 UTC
Permalink
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
2008-07-27 22:35:44 UTC
Permalink
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
2008-07-28 01:46:36 UTC
Permalink
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
2019-03-27 16:51:07 UTC
Permalink
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.

Loading...