%let pgm=odbc_withoutaccess;
I cannot test because I have the Access to PC-Files. I wonder if it
really works without access to pc-file product.
/* Importing a MS Access table without Access to PC-files
* I read that SAS now provides
sql connections, libname and 'proc import'
to excel with base SAS? I was unable to check
because I have access to pc-files.
The code below should work without access to
pc files?
* I will use a view in excel to import any
ODBC compliant database table?
ONLY TESTED WITH ACCESS
Here is an example of importing a MS Access
table into SAS, without access to pc_files;
1. Copy SAS supplied access database demo.accdb
2. In excel create a view of the odbc(access) table.
3. Set option to refresh table when opening excel
(not sure SAS opens excel )
4. Use passthru to excel to get names, type and length
5. Use libname to get odbc/oledb(access) table
* SAS ships with a sample access data base usually in
/SASFoundation/9.4/access/sasmisc/demo.accdb;
HAVE Access database with class table
(I create it so you can run the code)
Since you cannot create access database with the libname engine.
I will copy the SAS demo access database;
May be able to create an Access database with 'proc export'
but I prefer to use copy and just the libname and sql
connection.
x "copy c:\xxxxxxxxxxxxxxxx\SASFoundation\9.4\access\sasmisc\demo.accdb d:\mdb\class.accdb";
* create a table in the access database;
libname mdb access "d:/mdb/class.accdb" ;
data mdb.class;
retain id 0; * simple primary key enhances sql passthru;
set sashelp.class;
id=_n_;
run;quit;
libname mdb clear;
/*
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set MDB.class has 19 observations and 6 variables.
NOTE: DATA statement used (Total process time):
The CONTENTS Procedure
Data Set Name MDB.class Observations .
Member Type DATA Variables 6
Engine ACCESS Indexes 0
Variables in Creation
# Variable Type Len Format
1 ID Num 8
2 NAME Char 8 $8.
3 SEX Char 1 $1.
4 AGE Num 8
5 HEIGHT Num 8
6 WEIGHT Num 8
*/
WANT SAS7BDAT
Data Set Name WORK.CLASS
Member Type DATA
Engine V9
Variables in Creation Order
# Variable Type Len Format Informat Label
1 ID Num 8 ID
2 NAME Char 7 $7. $7. NAME
3 SEX Char 1 $1. $1. SEX
4 AGE Num 8 AGE
5 HEIGHT Num 8 HEIGHT
6 WEIGHT Num 8 WEIGHT
Up to 40 obs WORK.CLASS total obs=18
Obs ID NAME SEX AGE HEIGHT WEIGHT
1 1 Alfred M 14 69.0 112.5
2 2 Alice F 13 56.5 84.0
3 3 Barbara F 13 65.3 98.0
4 4 Carol F 14 62.8 102.5
5 5 Henry M 14 63.5 102.5
...
*/
SOLUTION (get type and length)
Create a view of the access table in excel
XLSX (actually a view of an access table)
Data Set Name XLS.class Observations
Member Type DATA Variables
Engine EXCEL
# Variable
1 ID
2 NAME
3 SEX
4 AGE
5 HEIGHT
6 WEIGHT
* get variable names - max of 26 but could increase;
* stops naturally after 6;
libname xls "d:/xls/vueclass.xlsx" header=no scan_text=no;
* this will only get the names that exist;
data header;
set xls.'class$A1:Z1'n;
;run;quit;
libname xls clear;
/*
Up to 40 obs WORK.HEADER total obs=1
Obs F1 F2 F3 F4 F5 F6
1 ID NAME SEX AGE HEIGHT WEIGHT
*/
* get type for each column.
You could generate this query from the header info;
I leave that to the programmer;
proc sql dquote=ansi;
connect to excel (Path="d:/xls/vueclass.xlsx" mixed=yes);
select * from connection to Excel
(
Select
count(*) as NumObs
,count(*) + sum(isnumeric(id)) as NumChrId
,count(*) + sum(isnumeric(name)) as NumChrName
,count(*) + sum(isnumeric(sex)) as NumChrSex
,count(*) + sum(isnumeric(age)) as NumChrAge
,count(*) + sum(isnumeric(weight)) as NumChrWgt
,count(*) + sum(isnumeric(height)) as NumChrHgt
from
class
);
disconnect from Excel;
Quit;
/*
NumChr* is the number of cells with type character
0 means the column is numeric
If NumChr= number of obs then all 18 cells have character data.
I
name and sex are character, the rest are numeric
NumObs NumChrId NumChrName NumChrSex NumChrAge NumChrWgt NumChrHgt
-----------------------------------------------------------------------------
18 0 18 18 0 0 0
*/
/* what is the max length of the columns */
/* You could generate this query from the header info */
proc sql dquote=ansi;
connect to excel (Path="d:/xls/vueclass.xlsx" mixed=yes);
select * from connection to Excel
(
Select
max(len(id)) as MaxLenId
,max(len(name)) as MaxLenName
,max(len(sex)) as MaxLenSex
,max(len(age)) as MaxLenAge
,max(len(weight)) as MaxLenWgt
,max(len(height)) as MaxLenHgt
from
class
);
disconnect from Excel;
Quit;
/*
MaxLenId MaxLenName MaxLenSex MaxLenAge MaxLenWgt MaxLenHgt
----------------------------------------------------------------------------
2 7 1 2 5 4
*/
Import Access table
libname xls "d:/xls/vueclass.xlsx";
data class;
set xls.class(dbsastype=
(
Id = 'numeric'
Name = 'char(7)'
Sex = 'char(1)'
Age = 'numeric'
Weight = 'numeric'
Height = 'numeric'
));
run;quit;
libame xls clear;
/* sashelp.class has 19 obs, I delete one in acess to
make sure excel dropped it without touching excel;
Up to 40 obs WORK.CLASS total obs=18
Obs ID NAME SEX AGE HEIGHT WEIGHT
1 1 Alfred M 14 69.0 112.5
2 2 Alice F 13 56.5 84.0
3 3 Barbara F 13 65.3 98.0
4 4 Carol F 14 62.8 102.5
5 5 Henry M 14 63.5 102.5
6 6 James M 12 57.3 83.0
7 7 Jane F 12 59.8 84.5
8 8 Janet F 15 62.5 112.5
9 9 Jeffrey M 13 62.5 84.0
10 10 John M 12 59.0 99.5
11 11 Joyce F 11 51.3 50.5
12 12 Judy F 14 64.3 90.0
13 13 Louise F 12 56.3 77.0
14 14 Mary F 15 66.5 112.0
15 15 Philip M 16 72.0 150.0
16 16 Robert M 12 64.8 128.0
17 17 Ronald M 15 67.0 133.0
18 18 Thomas M 11 57.5 85.0
*/
Am not sure what the question is. I have accessed MS-Access DBs with ODBC using
libname ac ODBC "myDatabase";
where myDatabase is an ODBC User Data Source Name set up with ODBC Data Source Administrator pointing to an Access database.
or with something like
proc sql;
connect to odbc as biota
(complete="dsn=MS Access 7.0 Database; dbq=c:\......\biota.mdb; readonly=TRUE");
create table cond as
select * from connection to biota (select * from Conditions)
order by espece;
disconnect from biota;
using the generic ODBC MS Access driver.
Interesting
Do you have the SAS Access to PC-FIles product?
Yes, I have a license to SAS/ACCESS to PC FIles. But ODBC connectivity is licensed separately as SAS/ACCESS Interface to ODBC.
Good to Know
I was hoping someone without any SAS Access products would test my code too see if it wouls import an Access table without licensing any SAS access products
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.