Add SAS Table Column with Default Value

[DavidCaliman]

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'

;

[stat_sas]

proc sql;

      alter table LIB1.TABLE1

      ADD FLAG char format = $1.;

   update LIB1.TABLE1

   set FLAG='N';

quit;

[RW9]

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.

[DBailey · Accepted solution]

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.

[LinusH]

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.

[Peter_C]

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.