20  SQL in SAS: Setup

20.1 Read Data

  • In the following example, we are reading the tab-delimited data (tblStudent.txt) on the SAS environment using the PROC IMPORT statement and assign the table name tblStudent.

  • You may have to check the delimiter option while calling PROC IMPORT for your data.

  • The arguments getnames=YES assign the variable names from the first row and guessingrows=MAX determines the length and data type of variables.

  • You may read the .CSV or .XLSX files using the appropriate PROC IMPORT commands.

  • You can also download the SAS binary data as a zipped file and use directly.

# Give your own path

libname SASData 'Path\To\Folder\data';

proc import datafile = 'Path\To\Folder\data\tblStudent.txt'
    dbms=dlm REPLACE out=SASData.tblStudent;
    delimiter='09'x;
    getnames=YES; 
    guessingrows=MAX;
run;

20.2 Run SQL Query

  • You can run SQL query in SAS using PROC SQL.

  • Include the quit statement to terminate the SQL procedure.

  • The macro variable put is included to check the total number of records in the log window.

# Provide your own path

libname SASData 'Path\To\Folder\data';

proc sql;
    SELECT *
    FROM tblStudent
;
quit;
%put NUMBER_RECORDS = &sqlobs;

20.3 SAS Script

SAS Script and log window

20.4 SAS Outputs

SAS Outputs