dm 'log;clear;out; clear;';
/*
Name: connect_ms-access.sas
Description: Get data from microsoft access database
Input: table_: name of table in ms-access file
output_ : Output data set name
input_ : path and name of ms-access file
select_ : =1 - use proc sql.
=2 - use impot.
=3 - use data step.
Output: A data set with name given to output_;
Example:
* Access file db1.mdb should be saved in the directory of C:\zhu_test\test11, ;
* otherwise change the path to where db1.mdb saved.;
%get_ids(table_=Students,
output_=temp1, input_=C:\zhu_test\test11\db1.mdb, select_=1);
History:
Date
Initial
Comments
04/15/06 Songlin
Zhu created
*/
%put %sysfunc(time(), timeampm8) %sysfunc(today(), mmddyy10.);
option linesize=175 pagesize=55 pageno=1;
%macro get_ids(table_=, output_=, input_=, select_=);
%if &select_. = 1 %then
%do;
libname acc access
"&input_."; /* libname acc access "&input_." dbpwd=password; */
proc sql noprint;
create table &output_. as
select *
from acc.&table_.
;
quit;
%end;
%else %if &select_. = 2 %then
%do;
proc
import dbms=access table="&table_."
out=&output_. replace;
/* dbpwd=password; if there is a password */
database="&input_.";
run;
%end;
%else %if &select_. = 3 %then
%do;
libname acc access
"&input_."; /* libname acc access "&input_." dbpwd=password; */
data &output_.;
set acc.&table_.;
run;
%end;
%else %put Error: select_ should be 1, 2 or 3.;
%mend get_ids;
%get_ids(table_=Students, output_=temp, input_=C:\zhu_test\test11\db1.mdb, select_=1);
proc print data = temp;
title 'temp';
run;
Above code
includes three methods to retrive data from MS-Access database.
To use this code, you need to save MS-Access file db1.mdb to the directory of c:\zhu_test\test11 or change the path to where the file saved.
If you have any questions, feel free to send me feedback.
|
