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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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