0

Relatively new to SAS and looking to bulkload data - pipe delimited .dlm - into a MySQL database. I've got the basics nailed down, I believe anyway, but I'm stumped on how to tell SAS the beginning and endings of a data row. Current syntax...

proc append base=abc.metrics (bulkload=yes 
                                                            bl_delimter='|'
                                                            bl_options='errors=0' 
                                                            bl_delete_datafile=no)
data=abc_metrics_&jid;
run;

Any thoughts, guidance, and comments would be appreciated.

Atwp67
  • 307
  • 5
  • 21
  • Did you bring this into a SAS dataset yet? – Joe Feb 14 '13 at 16:12
  • @Joe I did bring it into a dataset – Atwp67 Feb 14 '13 at 16:14
  • What do you mean by 'beginnings and ending of a data row', then? If it is in a SAS dataset, SAS has it in row format already. – Joe Feb 14 '13 at 16:18
  • @Joe Sorry, a little more clarity. I've written out the data to SAS data set as well as a dlm file with unique characters at the beginning and ending of each row: "!"=beginning and "~"=ending. A pipe character "|" separates each data field. Basically I'm looking for syntax - if it exists - that would tell SAS field/line termination points as you would in a SQL bulk load. – Atwp67 Feb 14 '13 at 16:23
  • Can you show your LIBNAME statement in there [masking ip/password of course]? Is this ODBC for example? Also, what version of SAS do you have? – Joe Feb 14 '13 at 16:28

2 Answers2

1

I don't think you can directly do this for MySQL: http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a002938849.htm http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001752476.htm

However, you can write out the file yourself. Change sashelp.class to your dataset, file "\server\yourfile.dlm", and if you need | after the last record prior to ~, remove that +(-1).

data _null_;
set sashelp.class;
file "c:\temp\test.dat" dlm='|' lrecl=32767;
put "!" @;
put name $ age height weight @;
put +(-1) "~";
run;

Then you can call MYSQL's bulk loading facility (whatever that is) as you normally would; you may be able to use a passthrough connection, ie,

proc sql;
connect to odbc (connection-information);
execute ("load file.dlm into table") by odbc;
quit;

(obviously bad sql syntax there, but that's the concept - anything in that " " is executed directly on the MySQL box as MySQL code as if you were in the DB's interface directly).

Or, if MySQL has something like Oracle's SQL*Loader, you can call that from the command line assuming you have access to it, using the 'x' command.

Joe
  • 62,789
  • 6
  • 49
  • 67
  • If I gather what you're saying I can pull from the actual SAS data set rather than the dlm file(?). – Atwp67 Feb 14 '13 at 16:28
  • Looking into it more, that doesn't seem to work for MYSQL. It works for SQL Server, but even Oracle doesn't work the way I thought it did. – Joe Feb 14 '13 at 16:30
  • libname abc odbc datasrc=abc user=? password=?; – Atwp67 Feb 14 '13 at 16:32
  • I don't think that SAS really supports bulk load to ODBC->MySQL. Looking at the documentation, it only lists Microsoft SQL Server as supported for bulk load via ODBC. You may need to put out the file manually in order to bulk load it (and then load it via passthrough SQL). Is your MySQL database local, and/or on a server that you can write files directly to (mapped folder etc.)? – Joe Feb 14 '13 at 17:02
  • MySQL is on a server but it appears I may have to go the traditional route on the SQL load but that said I'm going to try the "proc sql;" code. Thanks for your help on this issue! – Atwp67 Feb 14 '13 at 17:14
  • No problem. I'd recommend looking at the link in Bob's answer - it looks like it's got the more detailed PROC SQL method you need (ie, load data local infile). You can also easily create a .bat script [or unix .sh] to perform the load automatically, even if you do need to go the 'traditional route'. – Joe Feb 14 '13 at 17:16
1

As far as I know, you cannot directly "bulk load" from SAS to MySQL, See this note in the SAS Access manual.

So, your best bet is to create a delimited text file (perhaps with PROC EXPORT) and use a mysql utility. Here is a previous SO question with various solutions, particularly the one about the load data local infile command.

Community
  • 1
  • 1
BellevueBob
  • 9,498
  • 5
  • 29
  • 56