BookmarkSubscribeRSS Feed
DKAllgeier
Fluorite | Level 6

Hi,

I am trying to append observations from a SAS data set to a table in an ACCESS database. I have previously used this code in SAS 9.3 without any issue, but since updating to SAS 9.4M4, I am receiving an error message. The code is as follows:

 

proc datasets library=pace nolist;
append base=pace.tblLinkageLab
(sasdatefmt=(date_import='mmddyy10.' labdate='mmddyy10.'))
data=check.case_check_labs
force;
run;
quit;

 

'pace' is the Microsoft ACCESS database and 'tblLinkageLab' is the table into which I would like to append new observations from the SAS data set, 'check.case_check_labs'. There are 9 variables in the SAS data set and 11 in the ACCESS database, but even when I add the missing variables to the SAS data set the error persists.

 

The error message I receive is: ERROR: CLI execute error: [Microsoft][ODBC Microsoft Access Driver] You tried to assign the Null value to a variable that is not a Variant data type.

But I do not understand to what the error is referring. 

 

Thanks in advance.

5 REPLIES 5
Reeza
Super User

Is your libname assigned correctly?

Can you access data from the Access DB in the other direction?

DKAllgeier
Fluorite | Level 6

Yes, I am able to read data from the ACCESS database into SAS, but pushing it back out to ACCESS is an issue.

Reeza
Super User

Did it ever work in 9.4?

I would consider contact SAS tech support since it worked in one and not the other. 

 

First, confirm that there's no restrictions on your Access DB on any field with regards to allowing missing. If there is, you can limit your analysis to those variables in SAS and then check the corresponding variables for any missing values.  If that's the situation, it's likely your issue and the version change is coincidental. 

 

You could also test this theory by creating a small data set with no missing values and try uploading that - make sure you know how to remove it as well first though!

 

DKAllgeier
Fluorite | Level 6

After talking to SAS Tech Support, it wasn't clear what precisely was causing the problem. But they did suggest dropping the autonumber field in the base ACCESS table since it was likely the root of the issue. I revised my code adding the underlined option and was able to append the data set.

 

proc datasets library=pace nolist;
append base=pace.tblLinkageLab
(sasdatefmt=(date_import='mmddyy10.' labdate='mmddyy10.') drop=lab_id)
data=check.case_check_labs
force;
run;
quit;

 

The appended rows were numbered in the lab_id field sequentially, but with a large gap between the last existing record's lab_id number and the first added record's lab_id number, but in this case that does not represent a serious problem.  

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!

Discussion stats
  • 5 replies
  • 2590 views
  • 0 likes
  • 3 in conversation