Import data using Proc Sql, but error shows up

[cphd]

Hi all, 

I am trying to append dataset using proc sql, but it gives me an error. Could you please help me to solve this problem? Thanks!

proc sql;
create table ret.r1019 as
select *
from ret.vrrr2010
outer UNION corr
select *
from ret.vrrr2011
outer UNION corr
select *
from ret.vrrr2012
outer UNION corr
select *
from ret.vrrr2013
outer UNION corr
select *
from ret.vrrr2014
outer UNION corr
select *
from ret.vrrr2015
outer UNION corr
select *
from ret.vrrr2016
outer UNION corr
select *
from ret.vrrr2017
outer UNION corr
select *
from ret.vrrr2018
outer UNION corr
select *
from ret.vrrr2019
;
ERROR: Column 18 from the first contributor of OUTER UNION is not the same type as its counterpart
from the second.
ERROR: Column 18 from the first contributor of OUTER UNION is not the same type as its counterpart
from the second.
ERROR: Column 18 from the first contributor of OUTER UNION is not the same type as its counterpart
from the second.
ERROR: Column 18 from the first contributor of OUTER UNION is not the same type as its counterpart
from the second.
ERROR: Column 18 from the first contributor of OUTER UNION is not the same type as its counterpart
from the second.

quit;

[Tom · Accepted solution]

Your SQL code is just a  really long difficult way of doing this data step:

data ret.r1019 ;
  set ret.vrrr2010 - ret.vrrr2019 ;
run;

If you run that instead then, beside saving you a lot of typing, you will get a clearer error message.

Your error is that at least one of the variables is defined as numeric in at least one of those 10 input dataset and character in a least one.

So before trying to put the datasets together you need to make sure they are compatible.

Most likely you will want to fix the process that created them.  Did you perhaps use PROC IMPORT to create them?  If you used PROC IMPORT to create them from a text file (like a CSV file) then you can just replace that with a data step instead and you will be able to control how the variables are created. If you read them from Excel then you will probably have to do more work to fix the problem.

[cphd]

Thanks for the response.  I used the proc Import as each file was given in csv format, but has different field names, length, and format. 

If I use the code that you provided, can I append all the data without losing any?  Some files have more fields info than others. Also, will it solve non-matching between Char and Num problem? 

Worst scenario (or best), can I export the data to csv and then re-import them using infile code to solve the problem? 

Thank you in advance. 

[ChrisNZ]

As @Kurt_Bremser said, ensure your data is squeaky clean before trying to use it.

Your main job, regardless of the title you have (analyst, statistician, data scientist, whatever) is do ensure data quality.

That means ensure, not assume.

Always check it and validate it, assume it is not perfectly as planned or as documented.

This small effort at the start of your process will save you much time and many headaches.

In your case,

> but has different field names, length, and format. (and types too)

it seems that your data needs a good cleanup.

Do that, align all the fields as they should be (not as they come), and the rest will flow from there.

If you don't do that, you're setting yourself up for bad times.

[cphd]

Agreed. Will start from the beginning again. Thank you for your thoughtful suggestion. 

[Kurt_Bremser]

Go back to the step that creates all these datasets and make sure that it creates consistent dataset structures. Hint: do NOT use proc import in such a process.

Once you have a consistent structure, use a simple data step:

data ret.r1019;
set
  ret.vrr2010
  ret.vrr2011
  ......
  ret.vrr2019
;
run;