%macro utlodbs ( utitle=MS-Access Table to SAS Dataset, uobj=utlodbs, /*------------------------------*\ | Inputs | \*------------------------------*/ udsn=FOO, /* DSN */ uintbl=Products, /*------------------------------*\ | Outputs | \*------------------------------*/ uot=c:\obj, /* SAS Data Library */ uotmem=utlodbs1, /* SAS image of MS-Access Table */ /*------------------------------*\ | Temporary Outputs | \*------------------------------*/ uotperl=c:\utl\utlodbs.pl, /* dynamic temporary perl script */ uotflt=utlodbs.flt, /* file to pass data from perl to sas */ /* perl puts it in current active directory */ uflop=01.01.01 ) / des = "MS-Access Table to SAS Dataset"; /*----------------------------------------------*\ | | | This code requires | | | | %compress macro ( get it from utlmisc | | %utlfkil file delete utility =| | | | | \*----------------------------------------------*/ /*----------------------------------------------*\ | | | Win98 SAS7 MS-Access 97 | | | | This code loads an MS-Access table into a | | SAS dataset. | | | | Code may work with other databases without | | change. ( as long as an ODBC driver is avail | | and minimum ANSI SQL support ). | | | | | | Please feel free to clean up this code | | and repost or work with me to make my utls more| | robust and useful. | | | | PERL5, PERL ODBC modules and MS-ODBC (all free)| | are required. SAS Access to ODBC is not needed.| | | | Note PERL supports Oracle and Sybase | | | | Full ODBC functionality appears to be | | implemented in PERL5. | | | | | | It appears that support for Ansi SQL is | | very limited in MS-Access. Recently, I browsed | | a text on MS-Access 97 and there were only | | a few pages on MS-Access SQL. The article | | said that the missing SQL functionality was | | to numerous to list ( over 200 functions | | and constructs missing ). Notable among the | | missing functionality was data definition | | functions,ie SQLGetColattributes etc. | | I have chosen not to use VBA code. | | I hope to build code that can be used with any | | database. I hope when MS-Access matures the | | ANSI SQL functionality will be added. | | | | | | I do not expect anyone to use the following | | for code for production. This is why: | | | | 1. Limited exception and error checking | | in PERL script and SAS code. | | 2. This code has been tested with only | | one table, Products in the Northwinds | | database. | | 3. The algorithm for determining if a column | | is non-numeric may have bugs. | | Every column is checked for numeric type | | by issuing a SQL where clause | | 'where COLNAME = 2'. If the SQL return | | code is greater than 0 then the column | | is assumed to be character. | | 4. Not sure what will happen with other | | MS-Access datatypes like date. | | 5. The data is read twice. First to determine| | the maximum length of character columns, | | secondly to read data into SAS dataset. | | | |================================================| | | | This code will convert a MS-Access table | | to a SAS dataset. The SAS dataset label will | | have the date and the MS-Access table name. | | The SAS variable names are the MS-Access name | | with '_' substituted for blanks | | | \*----------------------------------------------*/ /*----------------------------------------------*\ | | | Preparatory work required to use this code: | | | | From memory my not be exactly right | | | | | | Get PERL | | ======= | | | | Go to http:www.activestate.com and download | | PERL 515 (ODBC modules are included) | | | | Read the install and readme documentation. | | | | Unzip and install PERL | | | | | | Create DSN for Northwinds Database | | ================================== | | | | Go to 32BitODBC in Win98 Control Panel | | | | Assign FOO as the DSN for Northwind | | database. | | | \*----------------------------------------------*/ /*----------------------------------------------*\ | | | Code by Roger DeAngelis | | | | Compucraft Inc | | 49 Spackenkill Rd | | Poughkeepsie, NY 12603 | | | | Office 914-463-2770 | | Fax 914-462-7595 | | | | Email xlr82sas@aol.com | | | | Users are free to do whatever they want | | with any or all of this code. | | | | Compucraft is not responsible for any | | problems associated with this code. | | | | Use at your own risk | | | \*----------------------------------------------*/ /*----------------------------------------------*\ | | | IPO | | === | | | | | | INPUT | | ===== | | | | The DSN you assigned (ie Northwinds Database) | | | | I assigned HOO to Northwinds | | | | Tablename in MS-Access ie Products | | | | | | PROCESS | | ======= | | | | Create a PERL Script ( uotperl ) that | | | | 1. Open the database | | 2. Create PERL ODBC code to dump MS-Access | | table to Flatfile. Future enancements | | might use pipes or file redirect. Pipes | | with SAS view would give a dynamic | | link to the MS-Access table. | | 3. Execute PERL script create commincation | | file. | | 4. Parse out Column nanes , column types s | | and character lengths from communication | | file. | | 5. Read data for keeps. | | | | | | The PERL Script can determine if a MS-Access | | column is numeic or character. Character | | type is assummed for all non numeric | | columns. | | | | | |================================================| | | | | | OUPUTS | | ====== | | | | | | | | uotperl PERL Script ( see put statements ) | | | | | | uotflt interface file PERL to SAS | | | | | | 10 numer of columns | | PRODUCTID MS-Access Column names | | PRODUCTNAME | | SUPPLIERID | | CATEGORYID | | QUANTITYPERUNIT | | UNITPRICE | | UNITSINSTOCK | | UNITSONORDER | | REORDERLEVEL | | DISCONTINUED | | 0 Column type | | 018 If gt 0 then character | | 0 | | 0 | | 018 | | 0 | | 0 | | 0 | | 0 | | 0 | | 1 data in blocks of 10 | | Chai | | 1 | | 1 | | 10 boxes x 20 bags | | 18.0000 | | 39 | | 0 | | 10 | | 0 | | 2 | | Chang | | 1 | | 1 | | 24 - 12 oz bottles | | 19.0000 | | 17 | | 40 | | 25 | | 0 | | | | uotmem SAS dataset from Access | | | | | | Observations: 77 | | Variables: 10 | | Indexes: 0 | | Observation Length: 116 | | Deleted Observations: 0 | | Compressed: N | | Sorted: NO | | | | | | | | | | -----Engine/Host Dependent Information----- | | | | Data Set Page Size: 8192 | | Number of Data Set Pages: 2 | | File Format: 607 | | First Data Page: 1 | | Max Obs per Page: 70 | | Obs in First Data Page: 56 | | | | -----Alphabetic List of Variables and Att | | | | # Variable Type Len Label | | ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ | | 4 Num CATEGORYID 8 CATEGORYID | | 10 Num DISCONTINUED 8 DISCONTINUED | | 6 Num UNITPRICE 8 UNITPRICE | | 8 Num UNITSONORDER 8 UNITSONORDER | | 7 Num UNITSINSTOCK 8 UNITSINSTOCK | | 1 Num PRODUCTID 8 PRODUCTID | | 2 Char PRODUCTNAME 32 PRODUCTNAME | | 5 Char QUANTITYPERUNIT 20 QUANTITYPERUNIT | 9 Num REORDERLEVEL 8 REORDERLEVEL | | 3 Num SUPPLIERID 8 SUPPLIERID | | | | | | | \*----------------------------------------------*/ /*-------------------------------------*\ | Delete intermediate files | \*-------------------------------------*/ %goto skp; %skp: %utlfkil(&uotperl); %utlfkil(&uotflt); /*-------------------------------------*\ | Create PERL Script for ODBC access | \*-------------------------------------*/ data _null_; file "&uotperl"; dsn="&udsn"; tbl="&uintbl"; flt="%upcase(&uotflt)"; /* current dir is used with this file */ /*-------------------------------------*\ | PERL ODBC STUFF CODED BY A SAS PRGMR | | THIS IS MY 2nd PERL PROGRAM | | | | More error checking and fewer lines | | of code are possible. | \*-------------------------------------*/ put 'use Win32::ODBC;' ; put '$db = new Win32::ODBC("' dsn +(-1) '");' ; put 'open ( uot, ">' flt +(-1) '") || die "DATABASE NOT OPENED";' ; put '$P=' tbl ';' ; put '$MaxRows = 1E10;' ; put '$db->SetStmtOption($db->SQL_MAX_ROWS, $MaxRows);'; put '$db->Sql("SELECT * FROM $P");' ; put '@F = $db->FieldNames();' ; put '$N = ($#F += 1 );'; put 'print uot "$N\n";' ; put 'for ( $i=0; $i<$#F; $i++ ) { $G[$i]=uc $F[$i]; print uot "$G[$i]\n";};' ; put 'for ( $i=0; $i<$#F; $i++ ) { $R[$i]=$db->Sql("SELECT $G[$i] FROM $P WHERE $G[$i] = 2"); print uot "0$R[$i] \n"; };' ; put '$db->Sql("SELECT * FROM $P");' ; put 'while ($db->FetchRow()){ undef %Data; %Data = $db -> DataHash();for ( $i=0; $i<$#F; $i++ ) { print uot "$Data{$F[$i]}\n"; } };' ; put 'close ( uot );' ; put '$db->Close();' ; put 'exit;'; stop; run; dm "inc '&uotperl'"; /* show perl script in display manager */ options noxwait xsync;run; %SYSEXEC PERL &uotperl ; /* execute perl script */ run; dm "inc '&uotflt'"; /* show interface data file in display manager */ data _null_; infile "&uotflt" length=ln end=done; /*-------------------------------------*\ | Number of columns in table | \*-------------------------------------*/ input ncols; call symput('uncols',compress(put(ncols,6.))); length sasname $ 32; /* sasname is quoted */ do i =1 to ncols; /*-------------------------------------*\ | Access Column Names | \*-------------------------------------*/ input colname $varying32. ln; nstr = compress(put(i,3.)); call symput( 'coln'!!nstr,quote(colname)); /*-------------------------------------*\ | Potential SAS column names | \*-------------------------------------*/ call symput( 'sasn'!!nstr,quote(colname)); end; do i =1 to ncols; /*-------------------------------------*\ | Column type $ for character | \*-------------------------------------*/ input colcode; if colcode gt 0 then colt='$'; else colt=' '; nstr = compress(put(i,3.)); call symput( 'colt'!!nstr,colt ); end; array colen(255) _temporary_ ( %do ui=1 %to 255; 0 %end; ); do until ( done ); do i =1 to ncols; /*-------------------------------------*\ | Maximum length for character variable | \*-------------------------------------*/ input; if ln gt colen{i} then colen{i} = ln; end; end; do i =1 to ncols; /*-------------------------------------*\ | Put lengths into macro vars | \*-------------------------------------*/ nstr = compress(put(i,3.)); call symput( 'colen'!!nstr,compress(put(colen{i},3.))); end; stop; run; %put _user_; %goto skp1; %skp1: libname uot "&uot";run; proc datasets library=uot nolist; delete &uotmem; quit; data uot.&uotmem ( label = "&sysdate &uintbl" ); infile "&uotflt" length=ln end=done missover; /*-------------------------------------*\ | Skip Descriptor Records | \*-------------------------------------*/ skiprecs = 1 + &uncols * 2; do i = 1 to skiprecs; input; end; attrib %do uj = 1 %to &uncols; %compress(&&sasn&uj,'"') label=&&coln&uj %if &&colt&uj eq $ %then length=$&&colen&uj; %else length=8; %end; ; do until (done); %do uj = 1 %to &uncols; input %compress(&&sasn&uj,'"') %if &&colt&uj eq $ %then $varying&&colen&uj... ln; ; %end; keep %do uk = 1 %to &uncols; %compress(&&sasn&uk,'"') %end; ; output; end; run; libname uot clear;run; *utlodbs; run; /* force the resolution of macro return arguments */ %mend utlodbs; %utlodbs(uintbl =Products,uotmem=ps);