Mass conversion of access data tables to sas dataset

[Talat]

Hi,

I have almost 11 sets (month specific) of 30 access datasets (.accdb), each having one table (the name of the dataset (1 to 30) and table (sunday-saturday) are different).  Need to covert them to SAS dataset.I can do it one by one but does anyone has  a macro that can be used to convert those.

All tables have exactly same variables with repeated information on same IDs. Even if I could somehow collapse all the access dataset as one then I could convert that final dataset to SAS.

Any help is highly appreciated.

Thx, talat

[SASKiwi]

I suggest you use a LIBNAME statement to read one Access database at a time, then use PROC COPY or PROC DATASETS with the COPY statement to copy all of the Access tables in that database in one go.

[Talat]

Can you please give a code as an example? At the end I need a macro code.

[Tom]

Show the code you use to convert one of the tables into a SAS dataset.

You can then use simple code generation to create code to that will read from different access tables and create different SAS datasets.

[Talat]

Hi Tom,

I have the following code:

%macro alridat(dat,tab);

pROC IMPORT OUT=Mydata.&tab.1
DATATABLE='&tab'
DBMS=ACCESS REPLACE;
DATABASE="c:\test\sep_18\&dat..accdb";
USEDATE=YES;
SCANTIME=NO;
DBSASLABEL=NONE;
RUN;

%mend;

%alridat(1,Sunday);

***********************************************************

I then can create a list of %alridat () commands using excel but surely I can do some sort of loop and array to get it done in sas to go through 1-30 for the files and for each file to search  if there are any table names with sunday to saturday to be read in as sas files.

Thanks, talat 

[SASKiwi]

Try this:

libname MyAccDB ACCESS "c:\test\sep_18\&dat..accdb";

proc datasets library = WORK nolist;
  copy in = MyAccDB out = WORK;
  run;
quit;

[Tom]

Looks like you have already started to try to convert the code that works for one file into a macro.

The posted macro probably will not work.  I doubt that your table in the Access file is literally named &tab (the macro processor does not process strings bounded by single quotes, use double quotes instead!).

So it sounds like you are saying there are 30 files you want to convert. 

What are the names of the files?  Are they literally '1.accdb' , '2.accdb' .... '30.accdb' ?

That is easy to code:

do i=1 to 30;
   name = cats(i,'.accdb');
   ...
end;

What are the names of the table(s) in the files?  Are they always named Sunday?  or does each file have multiple tables?  Does every file have the same number of tables?

Also what is with the folder that looks like it might be referencing some day in September?  

Or is that supposed to mean September 2018? (or perhaps September 1918?) then the 1,2,3,...30 are the days of the month in September?

[Talat]

Thanks Tom! I will try the suggestions you ans SASkiwi made.

[Talat]

What are the names of the table(s) in the files? Are they always named Sunday? or does each file have multiple tables? Does every file have the same number of tables?

- Files are  named as 1-31. Tables can be any of the weekdays. One file can have multiple tables.

Also what is with the folder that looks like it might be referencing some day in September?

- I have 11 folders each containing 28-31 files from Nov 17-Sept18. (nov 2017 to Sept 2018)