How can I avoid this error in SAS?

Question

When trying to merge datasets in SAS I continuously get the following error for a number of variables:

Column 115 from the first contributor of OUTER UNION is not the same type as its counterpart from the second

I've been able to get around this error usually by doing the following:

Changing one of the variables to the same "type" of the other. For example, changing variable A to a character type from a numeric type so that it matches the variable in the other dataset thereby allowing the merge to happen.
Importing the datasets that I am trying to merge together as CSV files and then adding the "guessing rows" option in the proc import step. For example:

proc import datafile='xxxxx'
    out=fadados
    dbms=csv replace;
    getnames=yes;
    guessingrows=200;
    run;

However, sometimes in spite of importing my files as CSVs and using "guessingrows" I still get the above error and sometimes there are so many that it is VERY time consuming and not feasible to actually convert all variables to the same "type" so that they match between datasets.

Can anyone advise me on how I can easily AVOID this error? Is there another way that people get around this? I get this error so often that I am tired of having to convert every single variable. There must be another way!

******UPDATE***** Here is an example that everyone is asking for:

  proc sql;
       title 'MED REC COMBINED';
       create table combined_bn_hw as
          select * from bndados
          outer union corr
          select * from hwdados;
    quit;

And here is the output I get in the log:

21019  proc sql;
21020     title 'MED REC COMBINED';
21021     create table combined_bn_hw as
21022        select * from bndados
21023        outer union corr
21024        select * from hwdados;
ERROR: Column 115 from the first contributor of OUTER UNION is not the same type as its
       counterpart from the second.
ERROR: Column 120 from the first contributor of OUTER UNION is not the same type as its
       counterpart from the second.
ERROR: Column 173 from the first contributor of OUTER UNION is not the same type as its
       counterpart from the second.
ERROR: Numeric expression requires a numeric format.
ERROR: Column 181 from the first contributor of OUTER UNION is not the same type as its
       counterpart from the second.
ERROR: Column 185 from the first contributor of OUTER UNION is not the same type as its
       counterpart from the second.
ERROR: Column 186 from the first contributor of OUTER UNION is not the same type as its
       counterpart from the second.
21025  quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

When trying to merge datasets in SAS...where is this trial? We need data and code to reproduce issue. — Parfait
Just use a normal data step to append the tables and you should get more helpful error messages. data combined_bn_hw; set bndados hwdados; run; It won't solve your problem but it will show you the variable names that have type conflicts. — Tom

Tom Tom · Accepted Answer · 2016-11-29T00:20:53

Don't use PROC IMPORT to guess what types of variables you have in your data. Its decision is going to depend on what values are in the file. Just write a data step to read your CSV files yourself. Then you can control how the variables are defined.

PROC IMPORT has to guess if your ID variable is numeric or character. And since it is doing based on what is in the file it can make different decisions for different sets of data. A common example is when a character variable is totally empty then PROC IMPORT will think it should be a numeric variable.

You could recall the data step code that PROC IMPORT generates and update that to use consistent data types for your variables. But writing your own is not very hard. you don't have to make as complicated a program as PROC IMPORT generates. Just include an INFILE statement, define your variables, including attaching any required INFORMATS (like for date values) and then use a simple INPUT statement.

data want;
  infile 'myfile.csv' dsd firstobs=2 truncover;
  length var1 $20 var2 8 ... varlast 8 ;
  informat var2 yymmdd10.;
  format var2 yymmdd10.;
  input var1 -- varlast; 
run;

How can I avoid this error in SAS?

2 Answers