My SQL Server table has a few default contraints:
[Name] [varchar](8) NOT NULL, [Sex] [char](1) NOT NULL CONSTRAINT [DF_TARGET_SEX] DEFAULT ('U'), [Age] [int] NOT NULL CONSTRAINT [DF_TARGET_AGE] DEFAULT ((-1)), [Height] [numeric](7, 1) NOT NULL CONSTRAINT [DF_TARGET_HEIGHT] DEFAULT ((-1)), [Weight] [numeric](7, 1) NOT NULL CONSTRAINT [DF_TARGET_WEIGHT] DEFAULT ((-1)), [valid_from] [datetime2](0) NOT NULL CONSTRAINT [DF_TARGET_VALID_FROM] DEFAULT (getdate()), [valid_to] [datetime2](0) NOT NULL CONSTRAINT [DF_TARGET_VALID_TO] DEFAULT ('9999-12-31 23:59:50'), [current_record] [char](1) NULL CONSTRAINT [DF_TARGET_CURRENT_RECORD] DEFAULT ('Y'), [DimensionCheckSum] [int] NOT NULL CONSTRAINT [DF_TARGET_DimensionCheckSum] DEFAULT ((-1)), [LastUpdated] [datetime] NOT NULL CONSTRAINT [DF_TARGET_LastUpdated] DEFAULT (getdate()), [UpdatedBy] [varchar](50) NOT NULL CONSTRAINT [DF_TARGET_UpdatedBy] DEFAULT (SUSER_SNAME()),
If I submit this in SSMS, all the above fields are auto-populated, and get their default values:
INSERT INTO tmp.target (Name) VALUES ('A')
When I have an ODBC connection to this table in SAS, and I execute the below code:
data foo; if 0 then set tmp.target (keep=name); Name='B'; output; stop; run; proc append base=tmp.target data=foo force nowarn; run;
All the default values are auto-populated, except for UpdatedBy and Sex.
If I use explicit pass-through it works as expected:
proc sql; connect using tmp; execute( INSERT INTO tmp.target (Name) VALUES ('C') ) by tmp; quit;
My ODBC libref is allocated as follows:
%macro allocate_sqlsvr_database(libref=,database=,schema=dbo); %* set connection string ; %let connect = NOPROMPT="Driver={SQL Server Native Client 10.0};Server=XXXXX,#####;Database=&database;Trusted_Connection=yes;"; %* set any libname options ; %let options = bulkload=yes schema=&schema reread_exposure=yes DBINDEX=YES UPDATE_LOCK_TYPE= NOLOCK; %* issue the libname statement ; libname &libref odbc &connect &options; %mend; %allocate_sqlsvr_database(libref=TMP, database=MyDatabase,schema=tmp)
Any ideas why these two default values aren't populated when using PROC APPEND? Esp. UpdatedBy? Could this be a bug?
SAS 9.3 on Windows...
Thanks...
There is the th BL_KEEPNULLS options that seems to be tailored for this problem. Unfortunately it seems only to be supported for OLEDB connections.
For ODBC you could try setting the IGNORE_READ_ONLY_COLUMNS to YES, if I recall right I think we solved this problem by doing that.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.