Help using Base SAS procedures

Add SAS Table Column with Default Value

Accepted Solution Solved
Reply
Contributor
Posts: 58
Accepted Solution

Add SAS Table Column with Default Value

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'

;


Accepted Solutions
Solution
‎12-01-2014 10:08 AM
Super Contributor
Posts: 578

Re: Add SAS Table Column with Default Value

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.

View solution in original post


All Replies
Trusted Advisor
Posts: 1,204

Re: Add SAS Table Column with Default Value

proc sql;

      alter table LIB1.TABLE1

      ADD FLAG char format = $1.;

   update LIB1.TABLE1

   set FLAG='N';

quit;

Super User
Super User
Posts: 7,392

Re: Add SAS Table Column with Default Value

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.

Solution
‎12-01-2014 10:08 AM
Super Contributor
Posts: 578

Re: Add SAS Table Column with Default Value

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.

Super User
Posts: 5,254

Re: Add SAS Table Column with Default Value

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.

Data never sleeps
Valued Guide
Posts: 2,174

Re: Add SAS Table Column with Default Value

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 3137 views
  • 7 likes
  • 6 in conversation