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;
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!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: