sas data set fails when one input is empty

2
votes

I have a SAS code where I use a data set command to combine 3 files. This week, we had a few instances where one of the input files had zero rows. This caused the file to not be formatted properly (all columns were numeric) and therefore the data step failed? Is there a way to override the requirement that column formats must be consistent if an input has zero rows?

data combined_file; set file1 file2 file3; run;

3
sas
Sounds like the problem is in the step that is making the datasets. What are you doing? Are you using something like PROC IMPORT to guess what is in the files? Why not write your own data step to create them so that empty rows don't cause the format to change? - Tom

3 Answers

3
votes

No, I don't think you can over-ride this behavior.

When you SET datasets together, SAS will throw an error if there is a variable that is numeric in one dataset and character in the other. This error is generated at compile time, before any records have been processed.

I agree with @Tom the best thing to do is probably to fix the root problem of why the dataset is being created with all numeric vars.

If you want a workaround, you might consider using the macro language to check whether or not a dataset is empty. You would end up a macro that conditionally generates the list of datasets to include on the SET statement, e.g.:

%macro ...;
  data combined_file;
    set
      %if %anyobs(file1) %then file1 ;
      %if %anyobs(file2) %then file2 ;
      %if %anyobs(file3) %then file3 ;
    ;
  run;
%mend;

If you want to try that workaround and want help writing %anyobs, just drop a comment and I'll post the one I use. It's based on Jack Hamilton's %MTANYOBS, as described in this paper: http://www2.sas.com/proceedings/sugi26/p095-26.pdf.

1
votes

I agree with Quentin on the problem, but I have a slightly different solution.

Rather than having a wrapper macro around some %if statements, write a macro for the set rows.

%macro checkAny(ds=);
  %if %anyobs(&ds) %then &ds. ;
%mend checkAny;

data want;
  set
    %checkAny(ds=file1)
    %checkAny(ds=file2)
    %checkAny(ds=file3)
  ;
run;

Of course with a %anyobs macro also as Quentin notes.

You could also change this to, instead of checking for observations, check for the columns to be the same. If you have a particular column pattern, you could write a macro to check dictionary.columns to verify that they're all the same.

0
votes

If the issue is clashing types for the same variable name across data sets, why not check directly for that?

PROC CONTENTS DATA=file1 OUT=var1 NOPRINT ;
RUN ;

PROC CONTENTS DATA=file2 OUT=var2 NOPRINT ;
RUN ;

PROC CONTENTS DATA=file3 OUT=var3 NOPRINT ;
RUN ;

PROC SQL ;

  SELECT COUNT(*)
    INTO :bad12
    FROM var1 v1
         INNER JOIN
         var2 v2
           ON UPCASE(v1.name) EQ UPCASE(v2.name)
    WHERE v1.type NE v2.type
  ;

  SELECT COUNT(*)
    INTO :bad13
    FROM var1 v1
         INNER JOIN
         var3 v3
           ON UPCASE(v1.name) EQ UPCASE(v3.name)
    WHERE v1.type NE v3.type
  ;

  SELECT COUNT(*)
    INTO :bad23
    FROM var2 v2
         INNER JOIN
         var3 v3
           ON UPCASE(v2.name) EQ UPCASE(v3.name)
    WHERE v2.type NE v3.type
  ;

QUIT ;

At this point you can check the to see if &bad12 + &bad13 + &bad23 EQ 0. If it is, proceed. If not, exit.