topic how to append dataset to mysql server in SAS Programming

how to append dataset to mysql server

leonzheng — Thu, 03 Jan 2019 22:30:20 GMT

I am trying to upload data into an existing table in mysql database, using append. I am able to pull data from that table which means connection should be OK. Here is my code for data pulling with database information:

proc sql;

connect to mysql (server="thisistheserver.com" port=3311

user="username" password="mypassword");

create table want as select * from connection to mysql

(

select *

from databaseschema.existingdata

);

disconnect from mysql;

quit;

Now below is my code to append:

libname DB mysql dsn = "thisistheserver.com" schema=databaseschema port = 3311 uid = "username" password = "mypassword";
proc append base = DB.existingdata data = have force;
run;

It returns error: error trying to establish connection: can't connect to local mysql server through socket
'/var/lib/mysql/mysql.sock' (2)

error: error in the libname statement.

error: libref DB is not assigned.

Please help me on this,

Thanks,

Re: how to append dataset to mysql server

r_behata — Thu, 03 Jan 2019 22:43:32 GMT

Check you Libname Statement, The SAS/Access Libname to Mysql should be like :

libname mysqllib mysql user=username password=mypassword database=databaseschema  server=thisistheserver.com port=3311;

Re: how to append dataset to mysql server

leonzheng — Thu, 03 Jan 2019 22:55:11 GMT

Thanks, now it seems libref is assigned and connection is established.

However, there is a new error in

proc append base = DB.existingdata data = have force;
run;

error: no database selected SQL statement: SELECT * FROM 'existingdata'.

I used append locally before and there was never such an error be seen.