☑ This topic is solved.
Need further help from the community? Please
sign in and ask a new question.
1 ACCEPTED SOLUTION
Accepted Solutions
Below some fully functional sample code that hopefully will show you the way.
/* options to write to the SAS log more info how SAS interacts with the DB */
options sastrace=',,,d' sastraceloc=saslog nostsuffix;
/* define common libname options */
%let pg_conn_common=
DATABASE=<pg database name>
SERVER="<pg server name>"
PORT=<port used by SAS to connect to PG>
authdomain=<authdomain as defined in SAS metadata - alternatively use user/password>
DBMAX_TEXT=32000
DIRECT_EXE=DELETE
DBCLIENT_MAX_BYTES=1
;
/* libname for permanent pg table to be updated */
/* - GLOBAL connection required to use pg temporar tables */
LIBNAME pg_perm POSTGRES
CONNECTION=GLOBAL
SCHEMA="<pg schema name of permanent master table>"
&pg_conn_common
;
/* libname for temporary pg table with updates */
/* - pg temporar table don't have a schema */
/* - pg temporary tables only exist within the scope of a session */
/* and are only accessible within the scope of a session */
/* (similar to SAS Work tables) */
LIBNAME pg_temp POSTGRES
CONNECTION=GLOBAL
DBMSTEMP=YES
&pg_conn_common
;
/* macro to drop tables if they exist */
%macro drop_tbl_if_exists(lref_tbl);
%if %sysfunc(exist(&lref_tbl)) %then
%do;
proc sql;
drop table &lref_tbl;
quit;
%end;
%mend;
/* drop tables to make sample code re-runnable */
/* - NB: PG temporary tables only exists during a session */
%drop_tbl_if_exists(pg_perm.class);
%drop_tbl_if_exists(pg_temp.class_updt);
/* load SAS master table into pg permanent table */
/* - SAS will create the pg table if it doesn't exist */
/* inspect SAS log more detail */
proc append base=pg_perm.class data=sashelp.class;
run;quit;
/* create table for updates in SAS Work */
data work.class_updt;
set sashelp.class;
if name='Alice' then
do;
age=99;
output;
end;
if name in ('Alfred','Jane') then
do;
weight=-10;
output;
end;
run;
/* load SAS update table into pg temp table */
/* - SAS will create the pg table if it doesn't exist */
/* inspect SAS log more detail */
proc append base=pg_temp.class_updt data=work.class_updt;
run;quit;
/* print pg temp table */
proc sql;
select *
from pg_temp.class_updt
order by name;
quit;
/* update master table with temp table */
/* - using explicit SQL pass-through */
/* - executes fully in-database; SAS just sends the code to the DB */
/* - SQL within the execute() block must be in Postgres syntax */
proc sql;
connect using pg_perm;
execute by pg_perm
(
update prepsegdr.class m
set age=t.age, weight=t.weight
from class_updt t
where m.name=t.name;
);
disconnect from pg_perm;
quit;
/* print pg master table after update */
proc sql;
select *
from pg_perm.class
order by name;
quit;
The "common" libname connection options might need to be different in your environment (like for dbclient_max_bytes). You find that documented here.
Easiest is normally to use an already existing and working libname as your starting point.
The bits that are not in the common section are MUST and will be the same for you.
I'm showing an example for an UPDATE but of course once you've loaded all your data into Postgres tables it's just Postgres SQL you need for any other operation.
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.
-
6051 views
-
2 likes
-
-
