I'm struggling with the syntax of creating a temp table in Postgres, as I do not have rights to create a permanent table. Here's what I tried:
libname mypg odbc DATAsrc=<data-source>
authdomain="<authdomain>"
schema=<schema>;
Libname works fine, and I'm able to query tables.
Temp table creation is where I get stuck:
proc sql;
create table mypg.'#pathway_set_tmp'n (pathwayid int);
Error received:
ERROR: Error attempting to CREATE a DBMS table. ERROR: CLI execute error: ERROR: syntax error at or near "#"; Error while executing
the query.
Thanks in advance!
Have you tried the SQL Pass-through facility ?
proc sql noerrorstop;
connect to postgres as x1(server=mysrv1 port=5432
user=mysur1 password='mypwd1' database=mydb1);
select * from connection to x1 (<Your P-SQL Query Here>);
disconnect from x1;
quit;
Thanks for the reply, I played around with that a little along the way - is there a way to specify the schema, and use windows authentication so I don't have to hard-code my user/pwd?
Thanks, could you be a little more specific in regard to exporting the table to Postgres as a temp table? Example would be much appreciated.
This link shows you how to create Postgres temporary tables: https://www.postgresqltutorial.com/postgresql-temporary-table/
If you use SQL Passthru and the Postgres examples in the link it should work. Something like this?
libname mypg odbc DATAsrc=<data-source>
authdomain="<authdomain>"
schema=<schema>;
proc sql noprint;
connect using mypg;
execute (CREATE TEMPORARY TABLE temp_table_name(MyTempTable);) by mypg;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.