How to create a table using `Proc SQL` without selecting from existing tables

user_6278424 0
Viewed 3351

What is the SAS version of "select from dual"? I want to create a table using Proc SQL without selecting from excisting tables. for instance. Basically I want something like:

PROC SQL;
  CREATE TABLE tmptable AS
  SELECT 1 AS myvar FROM dual;
QUIT;

This does not work. What choices do I have?

proc-sql sas sql
2 Answers

I don't think there is anything like select from DUAL in SAS. But you could try if this helps you:

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

The INOBS=1 makes sure you only read one row from sashelp.table, so you only have one result.

user_111311240

As you state, SAS Proc SQL does not have a premade DUAL table.

You can use CREATE and INSERT statements instead.

Example

proc sql;
  create table want (x num);

  insert into want values (1);

  insert into want 
    values(2)
    values(3)
  ;
quit;

or create your own DUAL first (perhaps if migrating SQL code into SAS Proc SQL)

proc sql;
  create table dual (dummy char(1)); insert into dual values ('X');

  CREATE TABLE tmptable AS
  SELECT 1 AS myvar FROM dual;
quit;
user_12499620
Related