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

rogerjdeangelis — Fri, 01 Jul 2016 15:18:37 GMT
%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                                                                                                                                                                                                          
*/
Re: Importing a MS Access table without Access to PC-files?

PGStats — Fri, 01 Jul 2016 21:27:46 GMT
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.
Re: Importing a MS Access table without Access to PC-files?

rogerjdeangelis — Sat, 02 Jul 2016 11:44:53 GMT
Interesting
Do you have the SAS Access to PC-FIles product?
Re: Importing a MS Access table without Access to PC-files?

PGStats — Sat, 02 Jul 2016 19:03:40 GMT
Yes, I have a license to SAS/ACCESS to PC FIles. But ODBC connectivity is licensed separately as SAS/ACCESS Interface to ODBC.
Re: Importing a MS Access table without Access to PC-files?

rogerjdeangelis — Sun, 03 Jul 2016 15:47:53 GMT
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
Re: Importing a MS Access table without Access to PC-files?

LinusH — Sun, 03 Jul 2016 21:22:38 GMT
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.
topic Importing a MS Access table without Access to PC-files? in SAS Programming

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

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

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

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

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

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