SAS - Add Computed Column/Variable in Proc Sql Create Table

Question

In MS SQL Server it is possible to define compute column in create table statement, e.g.

CREATE TABLE dbo.Products   
(  
    ProductID int
  , QtyAvailable smallint  
  , UnitPrice money  
  , InventoryValue AS QtyAvailable * UnitPrice  
);

Is there an equivalent option available in SAS proc sql?

The following throws syntax error:

proc sql;
CREATE TABLE work.Products   
( 
    ProductID num
    ,QtyAvailable num
    ,UnitPrice num format euro8.2 
  , InventoryValue AS QtyAvailable * UnitPrice  
);  
quit;

Allan Bowe Allan Bowe · Accepted Answer · 2017-06-13T09:06:56

It is not possible to define computed columns on a physical table in SAS. instead, one must create a physical base table for the data, and a view for the computed columns, eg as follows:

proc sql; 
create table work.products_base
( 
    ProductID num
    ,QtyAvailable num
    ,UnitPrice num format euro8.2 
);
CREATE view work.Products  as 
  select 
    ProductID 
    ,QtyAvailable 
    ,UnitPrice  
    ,QtyAvailable * UnitPrice as InventoryValue 
  from work.products_base;

insert into work.products
  set productid=1, qtyavailable=2,unitprice=3;

Trying to add a value for InventoryValue throws a warning:

169    set productid=1, qtyavailable=2,unitprice=3, inventoryvalue=4;
WARNING: Cannot provide InventoryValue with a value because it references a derived 
column that can't be inserted into.

Another approach is to use a constraint, which means one physical table, but it does require the developer to ensure the correct value is actually loaded into it (so it's not computed and takes up physical disk space).

proc sql; drop table work.products;
create table work.products_base
( 
    ProductID num
    ,QtyAvailable num
    ,UnitPrice num format euro8.2 
    ,InventoryValue num
    ,constraint InventoryValue check(InventoryValue = QtyAvailable * UnitPrice)
);
insert into work.products_base set qtyavailable=2,unitprice=2,inventoryvalue=4;
insert into work.products_base set qtyavailable=2,unitprice=2,inventoryvalue=2;

The second insert statement throws an error:

ERROR: Add/Update failed for data set WORK.PRODUCTS_BASE because data value(s) do not comply
       with integrity constraint InventoryValue.

Of course - if you are actually creating your table in SQL Server then you could use pass through syntax to create your computed column..

SAS - Add Computed Column/Variable in Proc Sql Create Table

1 Answers