BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DavidCaliman
Calcite | Level 5

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'

;

1 ACCEPTED SOLUTION

Accepted Solutions
DBailey
Lapis Lazuli | Level 10

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

5 REPLIES 5
stat_sas
Ammonite | Level 13

proc sql;

      alter table LIB1.TABLE1

      ADD FLAG char format = $1.;

   update LIB1.TABLE1

   set FLAG='N';

quit;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Lapis Lazuli | Level 10

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
Tourmaline | Level 20

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
Peter_C
Rhodochrosite | Level 12

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.

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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