topic Re: Postgres vs SAS in SAS Data Management

Postgres vs SAS

A_D — Wed, 08 Nov 2017 10:50:25 GMT

Hello,

I'm creating some ETL in data integration studio, my manager asked me to use postgres tables instead of SAS tables.

Can someone tell me what is the added value of postgres in SAS di?

thx

Re: Postgres vs SAS

ChrisBrooks — Wed, 08 Nov 2017 11:44:04 GMT

They're two entirely different things - Postgres is an object-relational database management system which supports things that SAS doesn't like triggers, user-defined objects stored within the database, a much larger number of native data types etc.

Of course if you have SAS Access for PostgresSQL or the appropriate ODBC driver then you can still run your SAS programs against data held in Postgres giving you the best of both worlds...

Re: Postgres vs SAS

A_D — Wed, 08 Nov 2017 12:59:22 GMT

Hi ChrisBrooks,

Thx for your answer.

All my source data are in oracle and sybase, I use odbc connection in order to create Librairies.

Can I bypass the Postgres tables and use only SAS views/tables in ETL?

Thanks

Re: Postgres vs SAS

LinusH — Wed, 08 Nov 2017 16:50:56 GMT

Not sure I agree to that description. In this context I would see PostgreSQL as a regular RDBMS.
Question if you can get a more detailed requirement than that.
Obviously you could easily switch so that your target tables reside in PostgreSQL. Question is what kind if usage this data will have. This is a major architectural decision.

Re: Postgres vs SAS

JuanS_OCS — Thu, 09 Nov 2017 10:14:22 GMT

Agreed with @LinusH,

probably the reason for that kind of decision is something as those tables/data in PostgreSQL would be used in another system, non-SAS.

Here my considerations:

- If you have your data origins in other data sources, as soon as you will make any query/join, SAS will bring all the data from those data sources into temporary SAS tables (SASWORK) and then make the operation/sql statement/data step. Hence, I recommend to ensure subsets from each data source are queried and then apply the transformation over those subsets.

- If you have ODBC connections, most probably you cannot use in-database operations (pass-through and such), but that is something more involved on this topic may answer you with more exact information.

- If you want to use the in-database performance features, you would need change in your SAS license the SAS/ACCESS to ODBC by SAS/ACCESS to (your database) and change your code/metadata library definition a bit to send the query to use the performance features.

Re: Postgres vs SAS

A_D — Thu, 09 Nov 2017 13:25:12 GMT

Hi JuanS_OCS,

Yes, I think that the postgres tables will be used by an other tool outside SAS.

Thanks for your answer.

Re: Postgres vs SAS

AhmedAccordBGro — Wed, 03 Jul 2019 10:06:14 GMT

Hi,

same case here, i got ODBC library working fine, I could import the postgres table metadata, but i cannot open any of them, it's giving error "ERROR: CLI describe error: E" all the time.

Any suggestions?