Want to import all tables from ms access using macro

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 78
Accepted Solution

Want to import all tables from ms access using macro

Dear Team,

I want to import multiple tabble from ms access using macro. Already i have cerated the same but not working prpoerly.

%let dbname =C:\my_data.mdb;
proc import out=work.objects
   datatable="msysobjects"
   dbms=access97 replace;
   database="&dbname";
   run;

data mstable (keep = name);
set objects;
where type in(1,6) and name not like 'MSys%';
run;
libname my_data "C:\my_data\";

data _null_;
set mstable end=lastrec;
call symput ('table'||left(_n_),compress(name));
if lastrec then call symput('ntable',_n_);

run;


%macro access_import;
%do i = 1 %to &ntable;
%input i=&i table=&&table&i;
proc import out= my_data.&&table&i
datatable = "&&table&i"
dbms=access97 replace;
database="&dbname";
run;
%end;

%mend access_import;

%access_import;


Accepted Solutions
Solution
‎06-17-2013 03:54 AM
Occasional Contributor
Posts: 18

Re: Want to import all tables from ms access using macro

Posted in reply to TarunKumar

Hi Tarun,

First create objects dataset and then Just copy past this code and run;

%let dbname =C:\my_data.mdb;

libname my_data "C:\my_data\";

%macro access_import;
proc import out= my_data.&tabl.
datatable = "&tabl"
dbms=access97 replace;
database="&dbname";
run;
%mend access_import;

data _null_;
set objects;

call symput ('tabl',trim(left(name)));

call execute('%access_import');
where type in(1,6) and name not like 'MSys%';
run;

View solution in original post


All Replies
Super Contributor
Posts: 334

Re: Want to import all tables from ms access using macro

Posted in reply to TarunKumar

You dont really mention what is happening. Are there error messages or just wrong output?

Not knowing how your access is setup the first thing to do is make sure the system tables (msysobjects is one of them) are visible to SAS, which I dont think is the default.

Here is a potential helpful reference

7495 - No read permission on System Tables when using SAS/ACCESS Interface toODBC with a Microsoft A...

If you are using Access 2007 + you may not be able to use those tables external to Access anymore.

The sas work around for that piece would be to use the sas dictionary table and the libname access syntax (which means you would need SAS/ACCESS interface for pc files).

libname out access "path to access db\data.accdb";

proc sql;

  select cnt into: cnt from

  (select libname, count(*) as cnt

  from sashelp.vmember

  where libname = "OUT" /*libname of the access database*/

  group by libname

  )

  ;

quit;

%put cnt = &cnt;

That might be the source of your issues but if not post more information about the problem.

EJ

Frequent Contributor
Posts: 78

Re: Want to import all tables from ms access using macro

Dear Ej,

Msysobject table is accessable and able to get the table name. macro is woking and no error msg is fired. Problem is that no data set is created.

Regards .............TK

Occasional Contributor
Posts: 18

Re: Want to import all tables from ms access using macro

Posted in reply to TarunKumar

Hi Tarunkumar,

libname my_data "C:\my_data\"; 

data _null_;
set mstable end=lastrec;
call symput ('table'||left(_n_),compress(name));
if lastrec then call symput('ntable',_n_);

run;

replace striked code with following code and see do you get your desired output.

new_var = '#'||'table'||trim(left(_n_))||'#';

new_var_1 = tranward(new_var,'#',"'");

call symput(new_var_1,trim(left(name)));

Frequent Contributor
Posts: 78

Re: Want to import all tables from ms access using macro

Posted in reply to Gaurang_sas

Hi Gurang,

still not working

error is :

 

WARNING: Apparent symbolic reference NTABLE not resolved.

ERROR: A character operand was found in the %EVAL function or %IF condition

where a numeric operand is required. The condition was: &ntable

ERROR: The %TO value of the %DO I loop is invalid.

ERROR: The macro ACCESS_IMPORT will stop executing.

Super Contributor
Posts: 282

Re: Want to import all tables from ms access using macro

Posted in reply to TarunKumar

Hi,

Do any records satisfy your where condition:

where type in(1,6) and name not like 'MSys%';


Check the Notes shown in the log to make sure data set mstable is not created with 0 observations as this could cause issues with the later code.


If mstable does have more than 0 observations then try displaying some of the values of the macro variables you are trying to use to make sure they are as you expect.


Using options like mprint and symbolgen can also help with diagnostics of your macro code.


Regards,

Amir.

Frequent Contributor
Posts: 78

Re: Want to import all tables from ms access using macro

Hi Amir,

This will store all table name of acess in sas data setr.

Regards

Tarun Kumar

Super Contributor
Posts: 282

Re: Want to import all tables from ms access using macro

Posted in reply to TarunKumar

Hi,

What is the purpose of the statement:

%input i=&i table=&&table&i;


Is it necessary? The documentation shows it being used in a different way:


SAS(R) 9.3 Macro Language: Reference

Try running your code without it.

Regards,

Amir.


Message was edited by: Amir Malik - format code

Super Contributor
Posts: 282

Re: Want to import all tables from ms access using macro

Posted in reply to TarunKumar

Hi,

Was:

%input i=&i table=&&table&i;

supposed to be a %put statement for diagnostics, e.g.:

%put i=&i table=&&table&i;

Regards,

Amir.

Occasional Contributor
Posts: 18

Re: Want to import all tables from ms access using macro

Posted in reply to TarunKumar

Hi Tarun,

can you send me the log not just the part of it?


Occasional Contributor
Posts: 18

Re: Want to import all tables from ms access using macro

Posted in reply to TarunKumar

Hi Tarun,

data mstable (keep = name);

set objects;

where type in(1,6) and name not like 'MSys%';

run;

What does objects dataset contain.

please let me know I will give you complete code for the same.

Solution
‎06-17-2013 03:54 AM
Occasional Contributor
Posts: 18

Re: Want to import all tables from ms access using macro

Posted in reply to TarunKumar

Hi Tarun,

First create objects dataset and then Just copy past this code and run;

%let dbname =C:\my_data.mdb;

libname my_data "C:\my_data\";

%macro access_import;
proc import out= my_data.&tabl.
datatable = "&tabl"
dbms=access97 replace;
database="&dbname";
run;
%mend access_import;

data _null_;
set objects;

call symput ('tabl',trim(left(name)));

call execute('%access_import');
where type in(1,6) and name not like 'MSys%';
run;

Frequent Contributor
Posts: 78

Re: Want to import all tables from ms access using macro

Posted in reply to Gaurang_sas

Hi Gaurgan,

Are you from india. pls share me your contact number i will call you.

Regards

Tarun Kumar

9899940978


Occasional Contributor
Posts: 18

Re: Want to import all tables from ms access using macro

Posted in reply to TarunKumar

9449629695

Frequent Contributor
Posts: 78

Re: Want to import all tables from ms access using macro

Posted in reply to Gaurang_sas

Thanks Gaurang


🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 14 replies
  • 2498 views
  • 2 likes
  • 4 in conversation