topic Add SAS Table Column with Default Value in SAS Procedures

Add SAS Table Column with Default Value

DavidCaliman — Mon, 01 Dec 2014 11:59:48 GMT

What command can I use to create a column in a SAS table and initialize it to default value 'N' ?

Example:

proc sql;

alter table LIB1.TABLE1

ADD FLAG char(1) format = $1. DEFAULT 'N'

;

Re: Add SAS Table Column with Default Value

stat_sas — Mon, 01 Dec 2014 13:11:07 GMT

proc sql;

alter table LIB1.TABLE1

ADD FLAG char format = $1.;

update LIB1.TABLE1

set FLAG='N';

quit;

Re: Add SAS Table Column with Default Value

RW9 — Mon, 01 Dec 2014 14:53:04 GMT

Just to add, as that is a flag you may run into issues if you default a value in rather than conditionally. I always have a three part case, Y if true, N if not true, else value (could be default or value). Otherwise defaulting in N might indicate something is not true, when missing for instance. E.g.

set FLAG=case when exists(select distinct A from B where A=5) then "Y"

when exists(select distinct A from B where A=4) then "N"

else "" end

If you default then A=1,2,3 would also give N, which could be misleading i.e. indicates they have a record=4.

Re: Add SAS Table Column with Default Value

DBailey — Mon, 01 Dec 2014 15:08:24 GMT

I couldn't find a way to add a true default constraint (i.e. one that creates a value on insertion). Looks like you'll need to insert a value or it will be missing unless you run the update statement after each insert.

Re: Add SAS Table Column with Default Value

LinusH — Mon, 01 Dec 2014 16:06:59 GMT

As https://communities.sas.com/people/DBailey mentions, there is no support i SAS for default constraints.

What you can do is to use CHECK or NOT NULL constraints to enforce the inserting process' to use (correct) values.

How will this/these tables be maintained?

If this is critical, and the value is hard to update/insert afterwards, consider using an external data source that supports default constraints.

Re: Add SAS Table Column with Default Value

Peter_C — Mon, 01 Dec 2014 17:25:39 GMT

the only default that SAS defaults is missing.

If you insist on seeing N then just apply a user format that supplies an N for missing.