Importing a flat file can be tricky if the first row contains null values!

Mark C Mar 26, 2005

We need to know that SAS uses the first row of data right after the header row as a guide for the values by column! So if the first row contains a blank value for a column, the rest of the values in that column will be ignored.

Here is a proc import code:

PROC IMPORT OUT= sasdata.custhistoricals
DATAFILE="historyFeb2005.xls"
DBMS=EXCEL2000 REPLACE;
RANGE="datax$";
GETNAMES=YES;
RUN;

One way to overcome this is to add a guide row as the first row after the header line. The guide row will have sample values of data by column so you can have both numeric and text values depending on the type of data. Once you import it, you just delete the guide row.

So the new code will have two more lines.

PROC IMPORT OUT= sasdata.custhistoricals
DATAFILE="historyFeb2005.xls"
DBMS=EXCEL2000 REPLACE;
RANGE="datax$";
GETNAMES=YES;
RUN;

data sasdata.custhistoricals; set sasdata.custhistoricals;

if productID eq ‘guide’ then delete;
run;

happy importing!

Back to SAS Techniques