topic Re: append dataset to mysql server in SAS Programming

append dataset to mysql server

leonzheng — Mon, 07 Jan 2019 21:50:46 GMT

I am trying to upload data into an existing table in mysql database, using append. Here is my code for data pulling with database information:

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

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

It return error as below:

ERROR: Prepare error: No database selected SQL statement: SELECT * FROM `existingdata`

Please help to check what is the problem with my "append" code, or if anyone can show me how to use insert into to do the job, it is also OK.

Thanks!
.

Re: append dataset to mysql server

SuryaKiran — Mon, 07 Jan 2019 21:57:16 GMT

Hello,

Does the base table exists in the database and you have permissions on it.

Re: append dataset to mysql server

jklaverstijn — Mon, 07 Jan 2019 21:59:09 GMT

As this is MySQL I would suggest:

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

Hope this helps,

-- Jan.

Re: append dataset to mysql server

leonzheng — Mon, 07 Jan 2019 22:03:33 GMT

My userid and password should give me permission. I can upload data in JSL. But right now I need to combine that part of code into my SAS code

Re: append dataset to mysql server

leonzheng — Mon, 07 Jan 2019 22:10:13 GMT

Yes, that is what I used

Re: append dataset to mysql server

Reeza — Mon, 07 Jan 2019 22:27:14 GMT

Can you access or see that table in the library?

Re: append dataset to mysql server

jklaverstijn — Mon, 07 Jan 2019 22:27:59 GMT

If you rerun your code with the following options you get some insight in the dialog between SAS and MySQL. This may help with troubleshooting:

options sastrace=',,,d' sastraceloc=saslog;

libname ...

Re: append dataset to mysql server

leonzheng — Mon, 07 Jan 2019 22:32:53 GMT

Here is the returned info

29
0 1546900232 no_name 0 APPEND
MYSQL_1: Executed: on connection 1 1 1546900232 no_name 0 APPEND
SELECT * FROM `existingdata` WHERE 0=1 2 1546900232 no_name 0 APPEND
3 1546900232 no_name 0 APPEND
4 1546900233 no_name 0 APPEND
MYSQL_2: Executed: on connection 2 5 1546900233 no_name 0 APPEND
SELECT * FROM `existingdata` where (0) 6 1546900233 no_name 0 APPEND
7 1546900233 no_name 0 APPEND
MYSQL: COMMIT performed on connection 2. 8 1546900233 no_name 0 APPEND
9 1546900233 no_name 0 APPEND
30 proc proc append base = DB.existingdata data = have force;
ERROR: Prepare error: No database selected SQL statement: SELECT * FROM `existingdata`.
31 run;

Re: append dataset to mysql server

leonzheng — Mon, 07 Jan 2019 22:34:05 GMT

I cannot directly. But I can pull data from the table in mysql with the same information

Re: append dataset to mysql server

Reeza — Mon, 07 Jan 2019 22:38:10 GMT

I'm not sure what "i can pull data form the table in mysql" means. Does this mean you can see the table in MySQL, but not in SAS?

Re: append dataset to mysql server

leonzheng — Mon, 07 Jan 2019 22:42:10 GMT

Sorry I couldn't explain myself clearly. I used to run my SAS code to get result. Then upload new dataset or pull the whole table from MySQL use JSL. Now I have to combine everything into SAS code. I tried pull whole table in a separate program and it worked, but uploading right now is not working. I think the access to the table in MySQL is OK, not sure if that is what you mean "see"

Re: append dataset to mysql server

Reeza — Mon, 07 Jan 2019 22:55:00 GMT

@leonzheng wrote:
Sorry I couldn't explain myself clearly. I used to run my SAS code to get result. Then upload new dataset or pull the whole table from MySQL use JSL. Now I have to combine everything into SAS code. I tried pull whole table in a separate program and it worked, but uploading right now is not working. I think the access to the table in MySQL is OK, not sure if that is what you mean "see"

Can you pull information from those tables in SAS? That's one way to verify your assumption that your access is ok.

Re: append dataset to mysql server

leonzheng — Mon, 07 Jan 2019 22:56:30 GMT

Yes, I can pull information from the table in SAS code

Re: append dataset to mysql server

Reeza — Mon, 07 Jan 2019 23:03:34 GMT

Here's how a SQL insert may work. The first data step is just to create a table to insert the data into.

data class;
set sashelp.class;
run;


proc sql;
insert into class
select * from sashelp.class;
quit;

Re: append dataset to mysql server

leonzheng — Mon, 07 Jan 2019 23:12:36 GMT

Follow your suggestion, I run code:

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

proc sql;
insert into DB.existingdata
select * from have //also tried select * from existingdata here
quit;

libname DB clear;

It still not working, show the same error: Prepare error: No database selected SQL statement: SELECT * FROM `existingdata`

Re: append dataset to mysql server

Reeza — Mon, 07 Jan 2019 23:16:36 GMT

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?

Re: append dataset to mysql server

leonzheng — Mon, 07 Jan 2019 23:18:30 GMT

I only changed the names I used for simplify reason

Re: append dataset to mysql server

Reeza — Mon, 07 Jan 2019 23:46:48 GMT

Sounds like a tech support question then.

Re: append dataset to mysql server

leonzheng — Mon, 07 Jan 2019 23:50:27 GMT

thanks

Re: append dataset to mysql server

kiranv_ — Tue, 08 Jan 2019 04:18:55 GMT

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.