Update MS Access table in SAS daily import

[mayasak]

Hi,

I tried to use the following codes (from a website) to import 10 MS Access files and both worked but there were two issues:

1. I have to run the codes for each of the10 files (2 tables each) separately. 
2. This has to be done on a daily basis.

My questions:

Is there a way to manipulate the codes to automate the code/s to import all the tables together?

Can I automate the process so that I don't have to manually import the files daily?

Here are the 2 codes below:

1.

proc sql;

connect to odbc as savesdb
(required="driver=Microsoft Access Driver (*.mdb, *.accdb);
dbq=c:desktop\saves.accdb;");
create table table1_copy as
select
*
from
connection to savesdb
(select * from table1);
disconnect from savesdb;
quit;

2.

libname savesdb odbc
required="driver=Microsoft Access Driver (*.mdb, *.accdb);
dbq=c:path\saves.accdb;";
data table1_copy;
set savesdb.table1;
run;

Thank you

[ballardw · Accepted solution]

It kind of depends on what you actually mean by "import".

When you connect to an external data source with a LIBNAME statement as long as that is in effect you can get to the data in the library. So if you have multiple Libname statements, such as in your SAS set up to always execute when the session starts then those libraries are available.

If the names of the tables and files involved do not change you may be able to create a batch file that runs at a scheduled time ever day (such as before you start work) so they are done without any manual steps. These would be run best with a program scheduler.

Or a program that starts up every time you start SAS. Which approach might be preferred depends on how long it takes to execute. If the program steps run in a minute or two then when SAS starts might be best. If you need something to execute even when you are not there or the moving data around takes a notable amount of time then the scheduler with a batch program might be better.

If the file name or table names change then you are going to have a hard time reducing your current approach.

[mayasak]

Hi ballardw,

Sorry, I just pressed on the solution tab instead of a reply. I'm not sure if you get my reply now. 

The names of the tables are just the same and do not change - I have a little issue there that the names have spaces and are not supported by SAS, but I'm trying to solve this through Access because I'm not sure if I can do that through SAS.

Both approaches that you recommended work for me. So is there any resource that help me in the programs you've mentioned?

Thank you 

[ballardw]

If you set:

options memname=Extend;

then SAS can handle some non-standard table names. You would use a name literal, which is the name of the table in quotes followed by an n, example: 'Table name with spaces'n  this will also handle other non-standard characters such as dash or slash and such. However, there is still a limit of 32 characters inside the quotes.

If the variable names inside the table aren't standard for SAS then you would add the option Validvarname=Any which would allow the same thing for variable names:  'non-standard char*name'n 

I would recommend a step to rename such just to avoid all the typos you may have while writing code and forgetting one of the quotes or having a space between the quote and the n.

Assuming that you have a library Somelib pointing to Access:

data mylib.standard_name;
   set Somelib.'Nonstandard name'n;
run;

Or proc copy. And then Proc Datasets or similar to modify the data set mylib.standard_name to rename any non-standard variables. Likely you will have to work with this manually to see the offending variables.

[mayasak]

Thank you ballardw.

This helps. I'll try this. 

Regarding the data import automation, is there any source that can help me create a program that starts up every time I start SAS. I have no clue on how to do this.

Thank you