0

From SAS, I am updating a table in MS Access with values from another table. Some of the fields in the table being updated contain spaces. This seems to be causing problems in the update statement. This gives me the error "Too few parameters. Expected 1.":

update [Original Table] as a inner join Updates as b on a.ID = b.ID
set a.[Variable 1] = b.[Variable 1]
where Year = "2000";

For field names without spaces, the statement works without error. And since I am using the field names elsewhere without table references/aliases, I figure the combination of [] and aliases is causing the problem. Any suggestions to address this?

BellevueBob
  • 9,498
  • 5
  • 29
  • 56
attitude_stool
  • 1,023
  • 1
  • 13
  • 18
  • Yes. If the set clause read `set a.var1 = b.var1` instead, the update statement would work. – attitude_stool Sep 24 '12 at 19:46
  • You mention SAS, but SAS does not allow spaces in variable names (at least not without special coding). Could that be the problem? Are you executing the above in a SAS program and if so, exactly what are you trying? – BellevueBob Sep 24 '12 at 20:35
  • @BobDuell, you are correct, SAS does not allow spaces in variable names. The tables, however, are housed in MS Access, which does allow spaces. The above example code would be contained in a pass-through: `proc sql; connect to access (path="C:/database.mdb"); execute ( update [Original Table] as a inner join Updates as b on a.ID = b.ID set a.[Variable 1] = b.[Variable 1] where Year = "2000" ) by access; disconnect from access; quit;` – attitude_stool Sep 24 '12 at 20:37
  • This may be a defect with SAS, possibly related to the version of SAS (or Access) you are using. What you are trying seems correct, so I would contact SAS Tech Support for help. – BellevueBob Sep 24 '12 at 21:15
  • Can your query as is be run in Access? If it runs in Access but not in SAS Passthrough, then it's a SAS problem. If it can't run in Access, then you either have a poorly designed query or something wrong with your version of Access. – Joe Sep 25 '12 at 14:58

3 Answers3

2

Year() is a function which returns a variant subtype integer which corresponds to the calendar year of the date value you give to the function.

In your case, it seems you have a field named Year. So perhaps the "missing parameter" is the expected date argument to the Year() function.

You can avoid confusing the db engine by enclosing Year in square brackets. The brackets signal the engine that Year is an object (field) name instead of the function.

update [Original Table] as a inner join Updates as b on a.ID = b.ID
set a.[Variable 1] = b.[Variable 1]
where [Year] = "2000";

Whenever possible, it's better to use names which don't conflict with reserved words. That may not be practical in your situation ... but if you can do it you will reduce the number of Access development headaches you will suffer. :-)

For further information about "naming challenges", see Problem names and reserved words in Access.

Sorry I overlooked the point that the query can work in spite of that WHERE clause issue.

I can't see anything about the remainder of your SQL which should trigger a complaint from the db engine. I assume you tested that statement directly in Access, and got no errors.

If there is something peculiar to the interaction between SAS and Access which causes this, perhaps you could use a saved Access query as a work-around. Take that SQL and save it as a named query, qrySasTest, in your Access db. Then try executing qrySasTest from the SAS side.

HansUp
  • 95,961
  • 11
  • 77
  • 135
  • Thank you for pointing that out, but the problem exists with and without the use of the where clause as stated in my original question. The problem literally occurs only when the field names in the set clause have spaces. – attitude_stool Sep 24 '12 at 20:31
1

This query worked as is for me (modifying only table names), both run from access and run from SAS. This is with SAS 9.3 64 bit and Office 2010 64 bit, so I suppose there could be something different going on with your version(s) of both, but it worked as expected.

proc sql; 
connect to access (path="c:\temp\test.accdb"); 
execute 
( 
update [Test2] as a inner join Test as b on a.ID = b.ID
set a.[Variable 1] = b.[Variable 1]
where Year ="2000";
) 
by access; 
disconnect from access; 
quit;
mjsqu
  • 5,151
  • 1
  • 17
  • 21
Joe
  • 62,789
  • 6
  • 49
  • 67
1

If you want to use the libname reference instead of SAS access, you can use the "dquote=ansi" option after your proc sql statement as shown below. In this example I created a library reference called mydbms:

libname mydbms odbc dsn=prompt preserve_names=yes;

    proc sql dquote=ansi;
    update mydbms."Original Table" as a inner join mydbms.Updates as b on a.ID = b.ID
    set a."Variable 1" = b."Variable 1"
    where Year = "2000";
    quit;
mjsqu
  • 5,151
  • 1
  • 17
  • 21
StatsStudent
  • 1,384
  • 2
  • 10
  • 28