Skip to content
Tutorials Center
My learning

Topics

Lesson 30 of 37 ยท SQL in SAS

PROC SQL: Creating Tables and Macro Variables

Create, change and drop tables in PROC SQL with CREATE TABLE AS, INSERT, UPDATE, DELETE and ALTER, and store query results in macro variables with SELECT INTO, with runnable SQL examples.

PROC SQL does more than read. It can create tables from queries, insert and change rows, add columns, and pass values to the macro language with select ... into :name. These abilities turn it into a general data-preparation tool and the bridge between data and macros.

SAS cannot run on this site; sas blocks are read-only. The TryIts run the same standard SQL in SQLite. Features that only exist in SAS (INTO :, FORMAT=, dictionary tables) appear in read-only blocks.

CREATE TABLE AS

In SAS, a SQL create table ... as select writes a data set:

SAS
proc sql;
  create table work.high_scores as
    select name, class, score
      from work.students
      where score >= 80
      order by score desc;
quit;

The new data set work.high_scores stores the result, with variable names, types and (for columns that came straight from a table) lengths and formats carried over. No report is printed. Use create view instead to save the query itself, which is re-run every time the view is read:

SAS
create view work.v_high as select * from work.students where score >= 80;

A view uses no storage and always shows current data, but pays the query cost each time. Use a table for results you reuse many times.

Creating an empty table and inserting

SQL CREATE, INSERT, UPDATE and DELETE

Press Run (or Ctrl + Enter) to see the result here.

In SAS the same statements exist with some SAS-flavoured syntax. A table is created with typed columns:

SAS
proc sql;
  create table work.products
    (sku char(4), name char(20), price num format=dollar8.2, stock num);

  insert into work.products
    values ("P1", "Pen", 1.5, 100)
    values ("P2", "Notebook", 4.0, 40);

  insert into work.products
    set sku="P3", name="Marker", price=2.5, stock=0;   /* SAS form */

  update work.products set price = price * 1.10 where sku = "P1";
  delete from work.products where stock = 0;
quit;

SAS types are only char(n) and num (plus date and others that map to numbers). Several values clauses may follow one insert, and the set form (column = value) is a SAS extension. As elsewhere in SAS, use double or single quotes for text, never backticks.

update and delete without a where change every row and cannot be undone. SAS prints a note, not a warning. Test with select and the same where first.

Changing structure: ALTER and DROP

SAS
proc sql;
  alter table work.products
    add discount num format=percent8.
    modify name char(30)
    drop stock;

  drop table work.products_backup;
quit;

alter table adds, modifies or removes columns. drop table and drop view delete the object. Because the data set is a file, anyone who has it open elsewhere can block these operations with a lock error.

Creating a table from another: copy shape without rows

SAS
create table work.empty like work.products;               /* same columns, no rows */
create table work.copy as select * from work.products;    /* same columns, all rows */

like copies the structure only, which is useful for building an append target.

Stand-in: CREATE TABLE AS in SQLite

SQL Create a table from a query, then summarize it

Press Run (or Ctrl + Enter) to see the result here.

SELECT INTO: query results in macro variables

The SAS-only into :name clause stores values in macro variables. It is the standard way to feed data into macro logic. (Macro variables were introduced in Macro Variables.)

SAS
proc sql noprint;
  select count(*)          into :n_rows trimmed from work.students;
  select max(score)        into :best   trimmed from work.students;
  select name              into :names  separated by ", " from work.students;
quit;

%put &=n_rows &=best;
%put All names: &names;
  • One row into one variable: select x into :var.
  • trimmed removes the leading blanks SAS adds when it converts numbers to text. Without it you may get 42, which breaks comparisons.
  • Several columns: select a, b into :a1, :b1 ....
  • Several rows into a numbered list: select name into :n1 - :n99 from ... creates n1, n2, ... and the automatic &sqlobs holds the number of rows read.
  • separated by joins all values into one macro variable, ideal for building in (...) lists:
SAS
proc sql noprint;
  select quote(strip(name)) into :list separated by ","
    from work.vips;
  create table work.vip_orders as
    select * from work.orders where customer in (&list);
quit;

Here the first step creates "Ada","Chen", and the second step uses it in a where. quote() adds the double quotes.

Stand-in: building an IN list

Python Stand-in: SELECT INTO ... SEPARATED BY

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The stand-in prints the generated where clause. A name that contains a quote would need extra care in real SAS code; quote() picks a safe quote character where it can, and macro quoting functions such as %bquote help when the text is used in macro code.

Where PROC SQL sits

Choose PROC SQL when you join, summarize and filter in one go, or need into :. Choose a DATA step when you need row-by-row logic, retain, first./last. or arrays. A realistic program uses both: SQL to shape and join, a DATA step for sequential logic, and PROCs for analysis. Chapter 8 returns to PROC steps for statistics.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does create table work.x as select ... do in PROC SQL?

    CREATE TABLE AS stores the result as a data set; no report is printed.
  2. 2. Why use the TRIMMED option in select ... into :var?

    Without trimmed, numeric values may carry padding blanks that break later comparisons.
  3. 3. Which clause puts all selected values into a single macro variable?

    separated by joins every row into one macro variable using the given delimiter.
  4. 4. What is the danger of UPDATE without WHERE?

    Without a WHERE clause, the statement applies to all rows.
  5. 5. What does create view store?

    A view saves the query definition, uses no data storage and always reads current data.

Summary

  • create table ... as select writes a data set; create view stores the query; like copies only structure.
  • insert, update, delete and alter table change data and structure; always guard with where.
  • SAS columns are char(n) or num.
  • select ... into :var trimmed and separated by pass query results to macro code.
  • Use SQL for joins and summaries, DATA steps for row-by-row logic.

Next: PROC UNIVARIATE and PROC TTEST