LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (March 2004, week 5)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Wed, 31 Mar 2004 13:25:40 -0500
Reply-To:     "Richard A. DeVenezia" <radevenz@IX.NETCOM.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Richard A. DeVenezia" <radevenz@IX.NETCOM.COM>
Subject:      Re: SAS and Postgres

Gregor wrote: > Hi! > > Has anoyone succeded to import data into SAS directly from Postgres > database? I've heard that someone succeded with MySQL through ODBC. > > Anyone? > > Gregor

Another, untested, alternative is to use xml.

Suppose you setup a web interface (cgi,php,asp,jsp,etc...) for querying your Postgresql database.

Let's say you have http://www.foo.com/login?d=database&u=username&p=password which emits a session id and http://www.foo.com/query?id=<session-id>&query=<query> (More complicated/secure queries could be posted to the query handler, not sure if SAS can post, but there probably is a way.)

Suppose <query> is SELECT ShipperID, CompanyName, Phone FROM Shippers

The query handler issues this selection statement to the database for XML output and emits it.

I.e. the results of SELECT ShipperID, CompanyName, Phone FROM Shippers FOR XML RAW

are now XML stream being delivered to a client (SAS in this case).

in SAS you would have

filename session url "http://www.foo.com/login?...."; data _null_; infile session; length id $100; input session; call symput ('session', session); stop; run;

* you will have to quote the non-macro &; filename psql url "http://www.foo.com/query?id=&session&query=SELECT ShipperID, CompanyName, Phone...."; libname psql xml; * when libname matches a filename, the contents of filename are interpreted as xml and rendered to sas system in the libname.memname data model;

So finally, you do something like

data work.shippers; set psql.shippers; run;

This technique would be applicable to *any* address that can emit xml (rss feeds, oracle, office, perl, php, jsp,a wireless router?) . Thus the beauty of xml.

-- Richard A. DeVenezia


Back to: Top of message | Previous page | Main SAS-L page