CODEWITHSUNDEEP
sqlsas
Magnus
Magnus

Reputation: 760

Can an SQL query select variables into different tables witn an INTO clause?

I'm working myself through this guide to how to convert several character variables at once to numeric using SAS. The guide uses PROC SQL, the SQL interface of SAS. What got me wondering is the part following the INTO-clause below:

proc sql noprint; select trim(left(name)), trim(left(newname)),
trim(left(newname))||'='||trim(left(name))
into :c_list separated by ' ', :n_list separated by ' ',
:renam_list separated by ' '
from vars;

Essentially, the clause seems to select each variable into a different table. Are you allowed to do this in SQL, or is this only a feature of PROC SQL in SAS?

I tried the syntax at several SQL sandboxes, but couldn't get it to work.

Upvotes: 0

Views: 474

Answers (3)

Richard
Richard

Reputation: 27518

Only the CREATE TABLE clause specifies the name of the table the query should create from the result set delivered by the SELECT clause.

The INTO clause in SAS' implementation of SQL is different than then INTO clause implemented in other flavors of SQL.

From SAS documentation

INTO Clause Stores the value of one or more columns for use later in another PROC SQL query or SAS statement.

Restriction: An INTO clause cannot be used in a CREATE TABLE statement.
...
INTO macro-variable-specification
< , … macro-variable-specification>

Compare the above with Microsoft Transact-SQL INTO clause

[ INTO new_table ]
...
new_table
Specifies the name of a new table to be created, based on the columns in the select list and the rows chosen from the data source.

For these two the general patterns are

  • SAS: CREATE TABLE abc as SELECT ...
  • MS: SELECT ... INTO abc

The SAS/MS comparison for selecting a column into a variable is

  • SAS (: indicates into variable)
    SELECT expression-1,...,expression-N
    INTO :macro-variable-1,...,:macro-variable-N
  • MS (@ indicates into variable)
    SELECT expression-1,...,expression-N
    INTO @:tsql-variable-1*,...,@tsql-variable-N

The variables the values are selected into have different scope contexts depending on system or implementation.

Upvotes: 2

Reeza
Reeza

Reputation: 21294

AFAIK there is no method to create multiple tables with a single query in SAS or ANSI SQL.

However, this is trivial within a SAS data step using a variety of methods.

The most basic:

   data male female;
      set sashelp.class;
      if sex='M' then output male;
      else if sex='F' then output female;
    run;

Upvotes: 1

PeterClemmensen
PeterClemmensen

Reputation: 4937

It is a feature of PROC SQL in SAS to read values into SAS Macro Variables.

Upvotes: 4

Related Questions