PROC IMPORT Will not Create Zero-Row Table

From sasCommunity
Jump to: navigation, search

If there are no rows of data in the source, PROC IMPORT will not create a data set. To illustrate, run this code:

filename have temp;

data _null_;
    file have;
    put 'a/b/c';
    *put '1/two/';
run;

proc import datafile=have out=sasds dbms=dlm replace;
    delimiter='/'; 
    getnames=yes;
    datarow=2; 
run;

The log reports

Unable to sample external file, no data in first 5 records.
NOTE: Import Cancelled.
NOTE: The SAS System stopped processing this step because of errors.

If the second PUT statement is un-commented, there is one row of data, and the import is successful. The log reports

1 rows created in SASDS       from HAVE.
NOTE: .SASDS was successfully created.

Here is a technique which forces PROC IMPORT to create an output table even if there are no rows of data in the source. It works with delimited text files.

filename extra_row temp;

%let lose_extra_row=;

data _null_;
    infile have end=nomore;
    file extra_row;
    input;
    put _infile_;
    if _n_=1 and nomore then do;
       dummyline = repeat('/',countc(_infile_,'/')-1);
       put dummyline;
       call symput(
         'lose_extra_row',
         'data sasds; modify sasds; remove; stop; run;'
                  );
    end;
run;

Ordinarly, the file referenced by EXTRA_ROW is an exact copy of the given file. However, the code checks for the situation in which the first record (the one containing the variable names) is also the last record. In that case, it counts the delimiters and creates an extra record containing that number of delimiters. This will create one observation consisting of missing values. The new file looks like this

a/b/c
//

CALL SYMPUT creates a macro variable containing code to remove this phony observation. Now PROC IMPORT can run against the new file

proc import datafile=extra_row out=sasds dbms=dlm replace;
    delimiter='/'; 
    getnames=yes;
    datarow=2; 
run;

The resulting table (SASDS) has one observation consisting of character missing values for the variables named in the header record. The process is completed by invoking the macro variable,which contains a DATA step to remove this bogus observation. However, SASDS persists.

&lose_extra_row

Of course if the original file contains data, the extra record is not created and the macro variable is null, so nothing is removed.

This could all be done within a macro. In that case it would be possible to skip the transcription and have PROC IMPORT run against the original file when the original file has data records.

Final housekeeping:

filename extra_row clear;

%symdel lose_extra_row;