Prev: Getting Base Observation values
Next: Getting Base Observation values
From: xlr82sas on 18 Feb 2010 04:30
Hi SAS-Lers,

Below is an R script the creates a R dataframe called x from a SAS
dataset called DMDEATH.

The advanage of the UODBC s that you do not need to have the SAS
ODBC server process running simultaneously and you can programatically
open and close the ODBC connection from R. Also you do not even need
SAS.

I installed the old SAS Universal ODBC driver, available on my
site. The UODBS is not longer on the SAS site. I think SAS remove it
because no other company has the nerve to charge for an ODBC driver?
Also it is not strategic(IOM/JDBC/OLE DB/ACTIVEX). You may have an
issue since the 30 day trial may have expired and you need to contact
SAS to get an extension or a registration key. Fortunately I still can
use it on my system. Go to the database section and toward the bottom
click on UODBC.

http://homepage.mac.com/magdelina/.Public/utl.html
http://tiny.cc/Wy5Eo
look for more on this in utl_tipweb this weekend

I believe it is a readonly driver. You do not need SAS on your
machine and the driver runs silently both interactive and batch. By
interactive I mean running R under SAS. This driver allows you to read
SAS datasets and you can then use the xportsas R package to create a
V5 file to return to SAS.

I set up a filedsn which points to just one SAS dataset. However
Richard Devenzia and I have code on our sites to programmatically
create dsns. ( I modified his code). Also you may be able to create a
system or user dsn to point to a folder

I ran using SAS 9.1.3 under windows 2000, don't laugh I like to let MS
products to age a little before I move up to XP. I tend to have alot
of great products from the past, before they became less functional
with enhancements.

To install RODBC, just type install.packages("RODBC"), from the R
command line.

Anyrate, here is the R code with some parts of the log(HMDEATH) is the
sas dataset.

library ("RODBC")
channel <- odbcConnect("sasu")
sqlFetch(channel, "HMDEATH")
sqlFetch(channel, "HMDEATH", max = 20, rows_at_time = 10)
x<-sqlFetchMore(channel, max = 20)
close(channel)

Here is some diagnostic stuff:

channel <- odbcConnect("sasu")
odbcGetInfo(channel)
odbcTables(channel, catalog = NULL, schema = NULL, tableName = NULL,
tableType = NULL, literal = FALSE)
odbcQuery(channel, query, rows_at_time = attr(channel,
"rows_at_time"))
odbcFetchRows(channel, max = 0, buffsize = 1000, nullstring =
NA_character_, believeNRows = TRUE)
odbcGetErrMsg(channel)
sqlColumns(channel, sqtable, errors = FALSE, as.is = TRUE, special =
FALSE, catalog = NULL, schema = NULL, literal = FALSE)
odbcClearError(channel)

Here some of the text from the R log

> channel <- odbcConnect("sasu")
> sqlFetch(channel, "HMDEATH")
_NAME_ _EST_ _STATUS_
1 G1FLAG 1.000000e+00 NA
2 FIXDEL0 1.000000e+00 NA
3 FIXMNU1 0.000000e+00 NA
4 G3FLAG 0.000000e+00 NA
5 FIXGE2 0.000000e+00 NA
6 FIXGAE2 0.000000e+00 NA
7 DELTA 0.000000e+00 0
8 THALF 1.905090e-01 1

> sqlFetch(channel, "HMDEATH", max = 20, rows_at_time = 10)
1 G1FLAG 1.000000e+00 NA
2 FIXDEL0 1.000000e+00 NA
3 FIXMNU1 0.000000e+00 NA
4 G3FLAG 0.000000e+00 NA
5 FIXGE2 0.000000e+00 NA
6 FIXGAE2 0.000000e+00 NA
7 DELTA 0.000000e+00 0
8 THALF 1.905090e-01 1

> x<-sqlFetchMore(channel, max = 20)
> close(channel)
>x
1 G1FLAG 1.000000e+00 NA
2 FIXDEL0 1.000000e+00 NA
3 FIXMNU1 0.000000e+00 NA
4 G3FLAG 0.000000e+00 NA
5 FIXGE2 0.000000e+00 NA
6 FIXGAE2 0.000000e+00 NA
7 DELTA 0.000000e+00 0
8 THALF 1.905090e-01 1

9 NU 1.437419e+00 1
10 M 1.000000e+00 0

SOAPBOX ON

With each new version of SAS we gain some functionality but lose
other key functionality. I don't think programmers ask SAS do do the
following.

1. In the 80's SAS 'proc matrix' , a precursor to IML was part of SAS
base.
2. In the 80's SAS supported 128bit numerics on IBM mainframes
3. In the 80's SAS attempted to make the DM editor as powerful as
IBM's ISPF. I have a lot of entries over the years asking SAS to
implement some of IBM's ISPF(late 70's early 80s) functionality ie X
and XX in the prefix area. I still find the old editor more powerful
the EE. Also simple editor command like 'delete ^'MPRINT", delete
lines that do not contain MPPRINT.
4. In the lat 90's and early 00s SAS supported the Universal ODBC
driver. I remember SAS-L posts asking SAS to add 'write' functionality
into the universal driver. But instead SAS pulled the driver off their
site. I think the issue is that SAS cannot charge for a ODBC driver
when no one else does.
5. EG is just another step backward. I think EG is the gateway to SAS-
SAP. The only thing I can say about SAP is that all SAP projects are
over budget and late. Actually I think SAS-SAP would lead to a lot of
opportunities for SAS programmers to clean up the mess.
6. Back in the 80's there were no starship enterprise this and warner
brothers studio that. The functionality was placed in SAS
proper(base). I do applaud SAS for not charging extra for 'proc sql'.

SOAPBOX OFF







 | 
Pages: 1
Prev: Getting Base Observation values
Next: Getting Base Observation values