jeudi 16 juin 2016

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

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 DocumentsMedicare_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-

Aucun commentaire:

Enregistrer un commentaire