BookmarkSubscribeRSS Feed
rogerjdeangelis
Barite | Level 11
%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 */
5 REPLIES 5
PGStats
Opal | Level 21

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
rogerjdeangelis
Barite | Level 11

Interesting

 

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

PGStats
Opal | Level 21

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

PG
rogerjdeangelis
Barite | Level 11

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

LinusH
Tourmaline | Level 20
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1852 views
  • 0 likes
  • 3 in conversation