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

Question

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?

score 3 · Accepted Answer · edited Sep 14 '20 at 10:31

3

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.

edited Sep 14 '20 at 10:31

Dirk Horsten

3,753
4
20
37

answered Sep 14 '20 at 08:29

JensR

130
7

score 1 · Answer 2 · answered Sep 14 '20 at 12:09

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;

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

2 Answers2