DATA Step, Macro, Functions and more

Importing a MS Access table without Access to PC-files?

Reply
Valued Guide
Posts: 505

Importing a MS Access table without Access to PC-files?

%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 */
Respected Advisor
Posts: 4,651

Re: Importing a MS Access table without Access to PC-files?

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. 

PG
Valued Guide
Posts: 505

Re: Importing a MS Access table without Access to PC-files?

Interesting

 

Do you have the SAS Access to PC-FIles product?

Respected Advisor
Posts: 4,651

Re: Importing a MS Access table without Access to PC-files?

Yes, I have a license to SAS/ACCESS to PC FIles. But ODBC connectivity is licensed separately as SAS/ACCESS Interface to ODBC. 

PG
Valued Guide
Posts: 505

Re: Importing a MS Access table without Access to PC-files?

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

Super User
Posts: 5,257

Re: Importing a MS Access table without Access to PC-files?

Ix this is a licensing issue I suggest that you contact your SAS saled representative for your site, or a SAS appointed retail sales partner.
Data never sleeps
Ask a Question
Discussion stats
  • 5 replies
  • 516 views
  • 0 likes
  • 3 in conversation