topic Re: Load data to ODBC Library with EG in New SAS User

Load data to ODBC Library with EG

PriitL — Thu, 02 Jan 2025 17:44:56 GMT

Hi!

I'm not sure if the subject location is correct but i'd give it a try.

So our main goal is to situation where user makes some workflow with EG and in the end it loads data to dataset which is actually ODBC type Library and linked to postgre database (already included with our SAS package).

At the moment we have a working solution where there's some data in Postgre DB table and we can read it from EG.

For example, there's 3 column table created and filled with pgAdmin:

When I open it from EG, I can see data inside it:

Now if I export it to csv file for example and add another line, save and try to Import the csv again with EG, the behavior is as expected:

As seen on the image there's a third record (10;pole;kirjet). But when opening the same table from pgAdmin, the record is still missing (exactly the same as on the first image). It means the data actually doesn't flow from EG to database.

What we are missing?

I tried to find similar solution and found Load SAS Dataset to MS SQL Server via SQL server job but it is not quite that. Basically the main goal would be the solution where we can import filtered and processed data and import it to database (probably automatically in the future). We also could use the solution where we take the whole sas7bdat dataset and import it directly to database but let's say it would be a backup plan.

Thanks!

Re: Load data to ODBC Library with EG

andreas_lds — Thu, 02 Jan 2025 17:53:20 GMT

Unfortunately the screenshots hardly tell us anything. Please post the log as text.

Re: Load data to ODBC Library with EG

Tom — Thu, 02 Jan 2025 18:32:26 GMT

You seem to be flipping the meaning of IMPORT and EXPORT back and forth during your description so I really cannot tell what you are trying to do. Perhaps if you used different verbs it would be clearer. Is your goal to push data from a SAS dataset into a Postgres table that already exists? Just us PROC APPEND if you have a libref already defined that points to the Postgres database.

libname target odbc .... connection details ...;
proc append base=target.tablename data=libref.memname ;
run;

Or perhaps use the INSERT statement of PROC SQL.

I also do not understand the digression into discussion of CSV files.

Re: Load data to ODBC Library with EG

Barkat — Thu, 02 Jan 2025 22:45:07 GMT

The following is for data in SQL server

LIBNAME YourLib ODBC noprompt="DRIVER={WriteYourDriverName};
Trusted_Connection=yes; server=ServerName;
database=YourDataBase" schema="YourSchema";
run;