append dataset to mysql server

[Reeza]

For some reason it seems to be thinking the existing data doesn't exist or is trying to run everything on the server. That's the exact code you're using?

[leonzheng]

I only changed the names I used for simplify reason

[Reeza]

Sounds like a tech support question then.

[leonzheng]

thanks

[kiranv_]

Try 

data work.a;

set DB.existingdata;

run;

Then see what happens. If this works your libname works, else you have issue with libname, you need to contact someone in your team to ask what they use and fix it

data db.a;

set work.a(obs=1);

run;

if above step does not work but your first step has worked then you have only read access to mysql table, but you do not write access. Which means you need to contact your database admin about this.

[leonzheng]

Good point!

I tried and failed at the 1st step, when I used the code below, there was no error reported so I thought the libname and connection should be OK. 

libname DB mysql user = userid password = mypassword schema=dbschema server="thisistheserver.com" port = 3311;

It seems do have some problems...

Here is the SAS code I used to pull data from DB.existingdata and it do work, please help me on how to upload data if possible,

proc sql;
connect to mysql (server="thisistheserver.com" port=3311 user=userid password=mypassword);
create table want as select * from connection to mysql
(
select *
from dbschema.existingdata
);
disconnect from mysql;
quit;

[Reeza]

That's using a pass-through query so it's not testing if the connection is set up correctly. If you cannot access it via a data step then the connection is not set properly. Also, note the dbschema.existingdata notation is different on the server in SAS SQL. Something that worked in pass-through may not work in SAS SQL.

[leonzheng]

Thanks,
Based the information I provided (and that is all the information I have) Can you point me a way of doing this?

[Tom]

As someone mentioned before there is no SCHEMA= option mentioned in the documentation for SAS/Access to MYSQL.

https://documentation.sas.com/?docsetId=acreldb&docsetTarget=n1lyg1d0crcejin1v7zaeteeniuu.htm&docset...

There is a DATABASE= option.  

Looks like you should be using that option instead.

libname DB mysql
  user = userid password = mypassword
  server="thisistheserver.com" port = 3311
  database=dbschema 
;

[leonzheng]

thank you, it worked, my mistake I didn't see the difference before. I marked the 1st answer about this as solution.