CODEWITHSUNDEEP
postgresqlsas
jingjing liu
jingjing liu

Reputation: 17

anybody has a sample code to import a SAS table into postgresql

I am looking to import a SAS table sas7dat format into postgresql. Or do i need to convert the table into csv then import? Thank you!

Upvotes: 0

Views: 2990

Answers (4)

Ian Gow
Ian Gow

Reputation: 3535

You can use my wrds2pg package for this: see pypi.org/project/wrds2pg

I don't have SAS, so I haven't used that functionality in a while (instead I connect to WRDS, which runs SAS for me).

Using wrds2pg will be much, much faster than using R for large tables. From the wrds2pg README:

The software can also upload SAS file directly to PostgreSQL. You need to have local SAS in order to use this function. Use fpath to specify the path to the file to be imported

Upvotes: 1

TC1
TC1

Reputation: 41

Another option might be to import the SAS file into R, then from R write the data to a SQL database using dbWrite.

Personally, I have found that getting data that is in R into a SQL database is easier than trying to get data from a text file into a SQL database.

See https://www.statology.org/import-sas-into-r/

https://dbi.r-dbi.org/reference/dbwritetable

Upvotes: 1

zernonia
zernonia

Reputation: 188

I found another solution which requires Python to import sas7bdat file into Postgres.

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("postgresql://user:password@localhost:5432/databasename", echo=False)
df = pd.read_sas('sas7bdat file location')
df.to_sql('tablename', con=engine, if_exists='replace')

Upvotes: 1

Kiran
Kiran

Reputation: 3315

If you have already SAS and Postgres at your organization then you probably you might have SAS/Access interface to Postgres. you can use proc setinit;run; to check whether you have SAS/Access interface to Postgres. If you have the SAS/ACCESS then you can use libname method as shown in below example.

/* define libname for postgres*/

libname A1 postgres server=mysrv1 port=5432 user=myusr1 password='mypwd1' database=mydb1;

/* define libname for SAS table*/

 libname mydata '/folders/myfolders/';

/* then use datastep or SQL to create your postgress table*/

 data A1.yourtable;
  set mydata.yourtable;
  run;

If you do not have SAS/ACCESS to postgres then you may have to do in 2 steps.(but check whether you have any etl tools available in your company)

first you have to use proc export to CSV. see the link below

Efficiently convert a SAS dataset into a CSV
then move csv data into postgres
How to import CSV file data into a PostgreSQL table?

Upvotes: 3

Related Questions