BookmarkSubscribeRSS Feed
RichM
Calcite | Level 5

Hi,

 

This proc append

proc append base = warehse.cmmn_data_secmaster data = WORK.securitymaster_trans force;   
run;

 

Is generating this error

 

WARNING: Variable SecurityMasterID was not found on DATA file.
WARNING: Variable exchange_country has different lengths on BASE and DATA files (BASE 3 DATA 6).
WARNING: Variable variable_id has different lengths on BASE and DATA files (BASE 30 DATA 13).
WARNING: Variable value has different lengths on BASE and DATA files (BASE 256 DATA 100).
ERROR: CLI execute error: [OpenLink][ODBC][SQL Server]Cannot insert explicit value for identity column in table
        'cmmn_data_secmaster' when IDENTITY_INSERT is set to OFF. (544) : [OpenLink][ODBC][SQL Server]General SQL Server error:
        Check messages from the SQL Server (544) : [OpenLink][ODBC][SQL Server]Attempt to bind to a non-existent column (544)

 

WORK.securitymaster_trans has these column

identifier

exchange_country

variable_id

value

 

The table warehse.cmmn_data_secmaster has the same columns but it also has SecurityMasterID which is an auto-incremented integer primary key. There are no additional columns.

 

Finally, my question

Why is proc append failing due to SecurityMasterID missing in the data set?  Obviously, we would want to the have the database create the PK integer.

 

Thanks in advance for you help!

3 REPLIES 3
ballardw
Super User

You may have to work with the admin for the SQL server because that variable, SecurityMasterID, sounds likely to be a key, and possibly a unique key, variable and apparently the data base requires its presence on any record in that table. So missing => not added at the SQL server side. I suspect that: when IDENTITY_INSERT is set to OFF is the SQL setting involved that would have to be changed on the base data table in SQL Server but that may not be appropriate if the values of the variable SecurityMasterID are supposed to be assigned/maintained elsewhere in the database.

 

Also you should notice that exchange_country is going to get truncated as you are attempting to add a 6 character value to a 3 character field.

 

RichM
Calcite | Level 5

Hi,

 

SecurityMasterID is definitely a unique key.  The strange thing is that this proc sql works.

 

proc sql noprint;

    INSERT INTO warehse.cmmn_data_secmaster (identifier,exchange_country,variable_id,value) VALUES ('rm2','rm2','rm2','rm2');

quit;

 

I believe that this tells me that the issue is not on the database since the insert correctly results in an auto-incremented value for SecurityMasterID in the database when it is not in the insert.  But I may be wrong.

 

I also know for sure that the dataset WORK.securitymaster_trans does not have a SecurityMasterID column.  Maybe I need to sort out how to see the IDENTITY_INSERT setting value prior to running the proc append.  The error says it is off but maybe it isn't.  Probably, grasping at straws with that thought.

 

Thanks,

Rich

 

 

 

Tom
Super User Tom
Super User

PROC APPEND is going to insert a full record.  See if you can create the same error by trying to insert like this.

proc sql noprint;
INSERT INTO warehse.cmmn_data_secmaster (SecurityMasterID ,identifier,exchange_country,variable_id,value) 
VALUES (.,'rm2','rm2','rm2','rm2')
;
quit;

Why not just use the INSERT command instead of the PROC APPEND?  So if your new records are in dataset named HAVE then you  want something like this.

proc sql noprint;
INSERT INTO warehse.cmmn_data_secmaster (identifier,exchange_country,variable_id,value) 
select identifier,exchange_country,variable_id,value 
from have
;

You could even generate that list of column names into a macro variable by querying the metadata for the table and excluding the variable SECURITYMASTER that you don't want to insert into.

 

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!

How to Concatenate Values

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.

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
  • 3 replies
  • 2335 views
  • 0 likes
  • 3 in conversation