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

Hello,

 

I have SAS 9.4 M2 and a database on a server with PostgreSQL 12.

We are using a libname with odbc to connect to the server. I can SELECT but I cannot DELETE datat from a table.

 

options nofmterr;

libname db_schema odbc user=**** password=**** datasrc=[database alias in odbc.ini] schema=[schema name] direct_exe=delete;

proc sql;
    /* SELECT WORKS */
    select * from db_schema.tbl_blah_error
    where blah_error_comment LIKE "TEST-1234%";

    /* ERROR - CANNOT DELETE */
    delete from db_schema.tbl_blah_error
    where blah_error_id=104680;
quit;
  1. I can select from the table just fine
  2. I cannot delete from the table, I get the following error
  3. I am told that this problem is expected, because
    1. we have postgresql 12 
    2. we need to upgrade sas because "they [SAS] wants us to use the psqlODBC driver that they supply with SAS instead of the one you install on the server [the database server?]"
    3. I see the driver file name (from our odbc.ini file) is psqlodbcw.so
  4. I cannot find any documentation that clearly supports #3 above
    1. for instance, I can't find a bug or hot fix or whatever that I would normally see for similar problems in the community forums (yay other community users!)
    2. we did find this note https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/whatsdiff/p1qjpyniex9epun17ie3st4n3ror.htm where it says "SAS 9.4M7 (August 2020) supports PostgreSQL 12." That implies but does not directly indicate that SAS 9.4 less than M7 does not support PostgreSQL 12.
    3. Can anyone point me to documentation listing which version of PostgreSQL SAS 9.4 M2 supports?
  5. If anyone has seen this problem, is there any known workaround?
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
Also try Pass-Through SQL.

proc sql;
connect to odbc(dsn=xx ........);
execute ( delete from ...........)
by odbc;
quit;

View solution in original post

4 REPLIES 4
yellowcat
Fluorite | Level 6
the error I get is
ERROR: Error updating table entry: Only SQL_POSITION/REFRESH is supported for PGAPI_SetPos
ERROR: ROLLBACK issued due to errors for data set HPTN071D.TBL_ASSAY_FILE_UPLOAD_ERROR.DATA.
NOTE: Deletes were rolled back by the DBMS software.
Ksharp
Super User
Also try Pass-Through SQL.

proc sql;
connect to odbc(dsn=xx ........);
execute ( delete from ...........)
by odbc;
quit;
yellowcat
Fluorite | Level 6

Thanks Ksharp! I used a connection and executed the query on the remote server.

However, I had to rewrite some of the queries, because they have correlations to local sas datasets.

ChrisNZ
Tourmaline | Level 20

SAS does not mandate any ODBC driver.

Ask the PostgreSQL admins which driver they recommend for your needs and install it on the SAS server.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1468 views
  • 1 like
  • 3 in conversation