proc sql problem with Access table via PCfiles

ucdcrush — Fri, 31 Jul 2015 21:37:08 GMT

Hi all.

I have a SAS program that references a table in an Access database using the PCFiles engine.

What I find is that, if I open the table in Access design view and add a variable, when I reference that variable in SAS (I have tried it in a WHERE statement), it does not work. And the SAS log does not indicate any problem, it will simply return 0 rows.

When I saw this happening, I tried 2 things:

1) Used a DATA step to create a copy of the Access table in the work folder (simply DATA TEST; SET [ACCESSTABLE]; Run;)

2) Used SAS proc SQL create to create a new table, with a new variable.

Using both of those approaches, I was able to use proc SQL and where to get the rows I was looking for (i.e., I changed nothing other than reference to the table or the variable). I concluded that if I do not create the variable in the Access table via SAS, it does not work.(I can't recall if I tried the variation whereby I would create the variable in Access, but use SAS proc sql create to create a new table using the existing table but adding a variable).

Here is the simply query that either returned rows (if SAS created the Access table variable) or didn't (if I created it in Access);

proc sql;

select md5_raw1 from elrdb.telr1_proc where compress(_match) = 'G' and imported2 ne 1;

quit;

When I look at the design view in Access, I cannot see with my eye any difference between the variable I would create there and the variable SAS creates. I have even copied the "row" (in design view, each variable is on a row) into a new row in order to maintain all the properties (double, length 8), and that didn't work.

Can anyone shed light on this? Is there any way to add a field in Access, and be able to correctly query it via SAS?

Thank you,

Dave

Re: proc sql problem with Access table via PCfiles

ballardw — Fri, 31 Jul 2015 22:37:37 GMT

Did you change the table design while SAS was connected to the data base? If so it may well be that SAS doesn't expect that to happen. If you disconnect and then reconnect (or restart SAS) does it then "see" the change you made while in Access.

Re: proc sql problem with Access table via PCfiles

ucdcrush — Fri, 31 Jul 2015 22:58:21 GMT

Hi - I think it may have been connected to that database when I made the change. However, I made the change in Access several days ago, and have since then stopped and started SAS several times, and it is not reading the field properly.

Re: proc sql problem with Access table via PCfiles

Reeza — Fri, 31 Jul 2015 23:08:50 GMT

The fields may be case sensitive would be my only suggestions. Also, what length is the new variable?

The other option is using SQL Pass through

topic Re: proc sql problem with Access table via PCfiles in SAS Procedures

proc sql problem with Access table via PCfiles

Re: proc sql problem with Access table via PCfiles

Re: proc sql problem with Access table via PCfiles

Re: proc sql problem with Access table via PCfiles