load data into SQL while datatype and column width in sas code?

Question

Hi I'm trying to load this data into mySQL server. It's a 2 Gb txt file, and the delimiter is tab.

I can use import data wizard, and choose file type Flat File Source to import it. But in this way I additionally need to tell the database about the datatype and length of each column.

Information about the datatype and length of each column is available in sas-infile code (downloaded together with the data). (I replaced code of many columns with .... for conciseness)

DATA Medicare_PS_PUF;
    LENGTH
        npi                                 $ 10
        nppes_provider_last_org_name        $ 70
        nppes_provider_first_name           $ 20
        nppes_provider_mi                   $ 1
        ....
        average_Medicare_standard_amt       8;
    INFILE 'C:\My Documents\Medicare_Provider_Util_Payment_PUF_CY2014.TXT'

        lrecl=32767
        dlm='09'x
        pad missover
        firstobs = 3
        dsd;

    INPUT
        npi             
        nppes_provider_last_org_name 
        nppes_provider_first_name 
        nppes_provider_mi 
        ....
        average_Medicare_standard_amt;
RUN;

I think maybe I should use the sas-infile code to load txt file into sas, and save it as sas-format data, and then import the sas-format data into mySQL.

My question is, can the info of datatype and length of each column be passed from SAS into mySQL ?

Any suggestion/other methods to handle this is appreciated. Thanks-

Dominic Comtois Dominic Comtois · Accepted Answer · 2016-06-15T08:45:20

One way would be to first submit a describe table query in proc sql. For example:

proc sql;
  describe table sashelp.class;
quit;

SAS Log shows:

create table SASHELP.CLASS( label='Student Data' bufsize=65536 )
  (
   Name char(8),
   Sex char(1),
   Age num,
   Height num,
   Weight num
  );

This will need to be adapted to MySQL's syntax -- labels removed, char may become varchar, num becomes int or float, and so on.

Then you could try inserting the data into the newly created MySQL table. Either by point-and-click through your MySQL interface, or if this is not possible, by generating a text file from SAS containing a series of insert into queries (using a file statement in a data step with the put statement to generate the appropriate queries), and then submitting this text file to your MySQL database.

Quick example (not tested):

data _null_;
  file 'insert_queries.sql';
  set sashelp.class;
  query = "insert into class values ("||
          catx(",", quote(trim(Name)), quote(Sex), Height, Weight)||
          ");";
  put query;
run;

load data into SQL while datatype and column width in sas code?

2 Answers