EZ Study

Actuarial Biology Chemistry Economics Calculators Confucius Engineer

SAS Tip: Read different delimited files into SAS

Question: How can I read different delimited(tab/comma) files into SAS? How can I read a file with ".trn" extenstion? How can I read excel file into SAS? What options should I use when I import data?

Answer: Here are a few common SAS example codes to read or output files from SAS, remember to change your file directory to run, it's been tested on SAS 9.1.3 and SAS 9.2. There is no special licence to read the csv or tsv files(it's included in

SAS Base), but there is special licence to read excel files or SPSS files directly, the licence is: SAS/ACCESS to PC files, you can run the code: proc setinit; run; to see if you have that licence on your SAS or not.

        /****            Use filename to read delimited files           ****/
        data dataout;
               infile "directory\a1.tsv"
               firstobs=2 /*get records from the 2nd row */
    MISSOVER /* input missing values to variables if no more values in record */
        /* turnover option to get specific values to variables instead of missing   */
        dsd /*(1): strip off any quotes that surround values in the text file

        (2): treat as missing values for consecutive delimiters.
        (3) by default, delimiter=comma. */

               dlm='09'x /*delimiter= tab/space, otherwise, use dlm=',' for comma */
               /*specify maximum length of the records, 256 bytes by default */
               HIT_TIMESTAMP_GMT :$20. /*specify the informat for the variables */
               ACCEPT_LANGUAGE :$20. ;run;

        /****      Read and output tab/trn/csv files from SAS        ****/
        /* read trn file into sas, also readable as long as tab seprated */
        proc import datafile='directory\a1.trn'
        out=v0 dbms=dlm replace; delimiter='09'x; datarow=2; run;

        proc import datafile="directory\a1.csv" out=dataout
               dbms=csv replace;
               delimiter='09'x;  /*use hexidecimal value '09'x for tab delimitor */
               datarow=2;         /* get data from 2nd row, 1st row for variable */
               guessingrows=100; run;
               /*get variables' format by the first 100 records, 20 by default; */

        proc export data=data1 outfile= "directory\csv_template.csv"
               dbms=csv replace; run;

        /****     Read and output SPSS(.sav) files from SAS               ****/
        /****     need to have licenece: SAS/Access to PC files               ****/
        /****     code to see if you have the licence: proc setinit; run;    ****/
        proc import datafile="directory\filename.sav"
               out=mydata dbms =spss replace;

        /****         Read and output excel files from SAS      ****/
    1)     libname lib1 oledb init_string="Provider=Microsoft.ACE.OLEDB.12.0;
        Data Source='directory\filename.xlsx';
        Extended Properties=Excel 12.0"; (14.0 for office 2010)

        data a0;     set lib1.'tabname$'n; run; /*don't forget dollar $ & 'n' */

You need to have the licence: SAS/ACCESS to PC files and SAS/ACCESS Interface to OLEDB to run the following procedure, you can run the code: proc setinit; run; to see if you have the licence. Or you can try the following two ways:

    2)     PROC IMPORT DATAFILE="directory\filename.xlsx"
        OUT=dataout DBMS=excel2010 REPLACE; RUN;

    3)     LIBNAME myexcel "directory\filename.xlsx";

      /**********Load dataset from ACCESS database **********/
      libname datalib "directory\databasename.mdb"; or use import procedure.

      proc import out=dataout datatable="tab's name" dbms=access replace;
      database="directory\databasename.mdb"; run;

Sometimes you may still find the errors if you try to read office2010 EXCEL/ACCESS database, it's possobly due to that you have 64-bit system enviroment, then you need to have the corresponding 64-bit office2010(by defauly, always install 32-bit office2010).

Click here to see more details if you have difficulties/errors in reading/loading excel files.

Related links:

Continue to next: How to read multiple files into SAS within one step?   SAS tutorial home
Back to: Change SAS Table view to see columns names, not labels   Statistics tutorial home