BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
A_D
Fluorite | Level 6 A_D
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
JuanS_OCS
Amethyst | Level 16

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.

View solution in original post

6 REPLIES 6
ChrisBrooks
Ammonite | Level 13

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...

A_D
Fluorite | Level 6 A_D
Fluorite | Level 6

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 

 

LinusH
Tourmaline | Level 20
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.
Data never sleeps
JuanS_OCS
Amethyst | Level 16

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.

A_D
Fluorite | Level 6 A_D
Fluorite | Level 6

 

 

AhmedAccordBGro
Fluorite | Level 6

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?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 2707 views
  • 1 like
  • 5 in conversation