BookmarkSubscribeRSS Feed
sophia_SAS
Obsidian | Level 7

Dear SAS community,

I would like to add a SAS dataset to an existing SQL Server table.  I would like to add the SAS dateset "adding" to the sql table "have."  I have been using the below code.

proc append base=SQLSERVER.have data=WORK.adding;

run;

However, when I run the code, I receive this error.

NOTE: Appending WORK.adding to SQLSERVER.have.

WARNING: Variable X1 was not found on DATA file.

WARNING: Variable X2 was not found on DATA file.

WARNING: Variable X3 was not found on DATA file.

NOTE: There were 1 observations read from the data set WORK.adding.

NOTE: 0 observations added.

NOTE: The data set SQLSERVER.have  has . observations and 44 variables.

ERROR: CLI execute error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit

       value for identity column in table 'have' when IDENTITY_INSERT is set to OFF.

NOTE: Statements not processed because of errors oted above.

I believe the errors are a result of two reasons.

1) I have variables (i.e. variables X1, X2, X3) in the SQL table that are not in the SAS dataset

2) I have one variable (i.e. variable X4) in the SQL table that is in a different format as that same variable in the SAS dataset

Is there a way to force the append without adding variables to the SAS dataset? And without changing the format of the variable shared by both sources?
Thanks!

7 REPLIES 7
DBailey
Lapis Lazuli | Level 10

Since you have an identity column in sql you need to specify the columns that you're inserting into:

proc sql;

insert into have (

     col1,

     col2,

     ....

     )

select

     col1,

     col2,

     ...

from want;

quit;

sophia_SAS
Obsidian | Level 7

When I try this method, I receive any error that says "ERROR: Attempt to insert fewer columns than specified after the insert table name.  What do you advise?  My base table 'Have' does have more variables than my SAS dataset "want".  I would like to keep it that way, so how to amend the SQL insert code to reflect the following scenario . . .

proc sql;

insert into have (

col 1,

col 2,

col 3

)

select

col1,

col3

from want;

quit;

DBailey
Lapis Lazuli | Level 10

You have to manually match up the columns in the two clauses.  You shouldn't include the identity column but only those that you have in your sas table.  I may have the table names reversed...but it sounds like you're inserting into the sql table and selecting from the sas dataset.

proc sql;

insert into sqllibname.sqltable (

     columns that match the sas dataset

)

     select

          column names in the same order as above

     from

          saslibrary.sastable;

quit;

sophia_SAS
Obsidian | Level 7

Thanks that clears it up!

OS2Rules
Obsidian | Level 7

Just use "force" on the PROC APPEND....

  proc append base=base.data

              data=work.data

              force;

  run;

sophia_SAS
Obsidian | Level 7

The force command did not work and I received the same error message as indicated in the original post.

Patrick
Opal | Level 21

I believe the FORCE option only manages the cases where you're having variables in source not existing in target. What you need is to have all target columns in source. This could be done via an additional mapping step, eg:

data work.data;

    set base.data(obs=0) work.data;

run;

proc append base=base.data

              data=work.data

              force nowarn;

  run;

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
  • 7 replies
  • 9199 views
  • 0 likes
  • 4 in conversation