Fifth columns from UNION are of different data type in SAS

I am a SAS Developer. I am using PROC SQL to perform union statement. My code:

proc sql;
create table test3 as
select a.state 
,a.station 
,a.ca_no
,a.applicant_name
,a.capacity
,a.commission_date
,a.technology
,a.pmu
,a.ppu
,a.ssu_pe
,a.re_switch_no
,a.voltage
,a.vcb_brand_and_model
,a.scada_y_n
,a.gps_coordinate
,a.plant_manager_phone_number
,a.plant_manager_name
,a.plant_manager_email
,a.highest_md_recorded_a
,a.highest_md_recorded_kw
,a.total_energy_sold
%do c=1 %to 12;
,a.kwh_&&ALLDATES&c..
%end;
%do c=1 %to 12;
,a.gen_factor_&&ALLDATES&c..
%end;
,a.period
from test a
union all
select b.pss_no as ca_no
,b.applicant_name /*capacity_mw voltage technology*/
,b.program
,b.scod_date 
,b.kick_off_date
from newresheet2 b;
quit;

As you can see, only ca_no in both table after the rename statement in table B block.

I am getting the error:

MPRINT(TRASPOSETRX): proc sql; MPRINT(TRASPOSETRX): create table test3 as select a.state ,a.station ,a.ca_no ,a.applicant_name ,a.capacity ,a.commission_date ,a.technology ,a.pmu ,a.ppu ,a.ssu_pe ,a.re_switch_no ,a.voltage ,a.vcb_brand_and_model ,a.scada_y_n ,a.gps_coordinate ,a.plant_manager_phone_number ,a.plant_manager_name ,a.plant_manager_email ,a.highest_md_recorded_a ,a.highest_md_recorded_kw ,a.total_energy_sold ,a.kwh_SEPT17 ,a.kwh_OCT17 ,a.kwh_NOV17 ,a.kwh_DEC17 ,a.kwh_JAN18 ,a.kwh_FEB18 ,a.kwh_MAR18 ,a.kwh_APR18 ,a.kwh_MAY18 ,a.kwh_JUN18 ,a.kwh_JULY18 ,a.kwh_AUG18 ,a.gen_factor_SEPT17 ,a.gen_factor_OCT17 ,a.gen_factor_NOV17 ,a.gen_factor_DEC17 ,a.gen_factor_JAN18 ,a.gen_factor_FEB18 ,a.gen_factor_MAR18 ,a.gen_factor_APR18 ,a.gen_factor_MAY18 ,a.gen_factor_JUN18 ,a.gen_factor_JULY18 ,a.gen_factor_AUG18 ,a.period from test a union all select b.pss_no as ca_no ,b.applicant_name ,b.program ,b.scod_date ,b.kick_off_date from newresheet2 b; WARNING: A table has been extended with null columns to perform the UNION ALL set operation. ERROR: Column 5 from the first contributor of UNION ALL is not the same type as its counterpart from the second.

I checked on the datatype for ca_no in both table and both are character. When i count the fifth columns from table A, which is the capacity, i do not have a column called Capacity in table B. In fact i commented out Capacity_MW from table B which do not share the same name. Is this the cause?

CREATE TABLE test3 AS

SELECT
   a.STATE
 , a.station
 , a.ca_no
 , a.applicant_name
 , a.capacity
      --------- more than 5 -----------
 , a.commission_date
 , a.technology
 , a.pmu
 , a.ppu
 , a.ssu_pe
 , a.re_switch_no
 , a.voltage
 , a.vcb_brand_and_model
 , a.scada_y_n
 , a.gps_coordinate
 , a.plant_manager_phone_number
 , a.plant_manager_name
 , a.plant_manager_email
 , a.highest_md_recorded_a
 , a.highest_md_recorded_kw
 , a.total_energy_sold
 , a.kwh_SEPT17
 , a.kwh_OCT17
 , a.kwh_NOV17
 , a.kwh_DEC17
 , a.kwh_JAN18
 , a.kwh_FEB18
 , a.kwh_MAR18
 , a.kwh_APR18
 , a.kwh_MAY18
 , a.kwh_JUN18
 , a.kwh_JULY18
 , a.kwh_AUG18
 , a.gen_factor_SEPT17
 , a.gen_factor_OCT17
 , a.gen_factor_NOV17
 , a.gen_factor_DEC17
 , a.gen_factor_JAN18
 , a.gen_factor_FEB18
 , a.gen_factor_MAR18
 , a.gen_factor_APR18
 , a.gen_factor_MAY18
 , a.gen_factor_JUN18
 , a.gen_factor_JULY18
 , a.gen_factor_AUG18
 , a.period
FROM test a

That part has many more than 5 columns: the following part does have 5 columns:

UNION ALL

SELECT
   b.pss_no AS ca_no
 , b.applicant_name
 , b.program
 , b.scod_date
 , b.kick_off_date
FROM newresheet2 b;

A union requires the same number of columns in each subquery, and that each of those columns must be of "compatible" data types (e.g. integer will go into decimal column, but varchar will not go into a date column).

Are EACH these column pairs compatible?

SELECT
   a.STATE
 , a.station
 , a.ca_no
 , a.applicant_name
 , a.capacity
FROM test a

UNION ALL

SELECT
   b.pss_no         AS ca_no
 , b.applicant_name
 , b.program
 , b.scod_date
 , b.kick_off_date
FROM newresheet2 b;

"Alignment" of columns is NOT achieved by column name/alias, but by position in the select clause, below the a.ca_no is aligned to b.pss_no, and a.applicant_name is aligned to b.applicant_name

SELECT
   a.ca_no
 , a.applicant_name
FROM test a

UNION ALL

SELECT
   b.pss_no         AS ca_no
 , b.applicant_name
FROM newresheet2 b;

[PDF] 242-31: SQL Set Operators: So Handy Venn You , When it comes to combining data from multiple tables in the SQL Procedure, joins UNION). The fact that the DATA step and PROC SQL place the columns in the same 5 the log message is. ERROR: Column 2 from the first contributor of OUTER There is just one set operator which is immune to data type mismatches  The UNION operator in Proc SQL is used to append the rows of two or more SELECT statements having the same number of columns with similar data types. Similar columns in each SELECT statement should normally be in the same order, as the Union is done by position not by column name, except where appending dissimilar columns from a select

The issue is that the query before UNION ALL is having more number of columns projected than the query after the UNION ALL operator.

You need to make sure that both the queries select same number of coulmns.

Combining Tables Vertically Using PROC SQL - 5 of 33, SAS OnlineTutor®: Advanced SAS® Combining (The set operator OUTER UNION does not overlay columns.) X had a different data type than column Two. However, the data type of the corresponding columns must match or the union will not occur. PROC SQL issues a warning message and stops executing. The names of the columns in the output table are the names of the columns from the first table-expression unless a column (such as an expression) has no name in the first table-expression.

simplest way to fix this problem is to append using set statement and it will appends using column name rather column position

   /*create table example of your first dataset. table with more columns*/
   data class1;
  set sashelp.class;
  run;

 /*create table example for your second dataset with fewer column and a different name*/

     data class(keep = name age gender);
    set sashelp.class;
    gender =sex;
     run;



   /* append it using set statement using rename. same name column append together, 
    missing values for other columns where there is no match*/

    data want;
   set class1 class(rename = (gender=sex));
   run;

you can also use proc append, it also appends using column name rather column position.

[PDF] SAS 9.4 SQL Procedure User's Guide, It is a SAS file of type DATA. PROC. SQL tables consist of rows and columns. The rows correspond to observations in SAS data files, and the columns  If your data is already in 64 SAS tables, each table will have consistent types for each column, and you can tell the type from proc contents of the data table. So even if the value looks like a character value (eg 12MAR45) it will just be the formatted representation of the underlying numeric value.

Combining Tables Vertically with PROC SQL, UNION is performed by position not by column name. Hence, common columns in each SELECT statement should be in the same order. If CORR keyword is  Data Type Compatibility The alignment of columns in these examples has worked smoothly because the aligned columns have matched with respect to data type (numeric or character). Since column alignment is an essential aspect of almost all of the set operators, it’s worth exploring this a bit more. We’ll need some test data sets with

[PDF] Proc SQL versus The Data Step, demonstrated with UNION ALL, UNION DISTINCT and JOINS. Three types of join will be discussed, EQUIJOINS, based on one common value, INNER JOINS,  Re: Join 2 tables with Different Field type types Posted 08-11-2010 (7502 views) | In reply to thegraduate I suggest you use the computed column option, and the Advanced Expression Editor to type in the INPUT function as I have already mentioned, to create a new computed column that converts the character column to numeric.

Reading, Combining, and Modifying SAS Data Sets : Combining , If the input data sets contain different variables, observations from one data Data Set CONCATENATION 1 Obs Common Animal Plant Number 1 a Ant 5 2 b Bird . from year1 outer union corr select * from year2; select * from combined; quit;. Hi Forum, I'm using SAS Enterprise Guide 4.3 with Windows 7 in case it's useful. I have 2 tables, with a Month_Id written as '201310' to denote October 2013 for example. Problem is, I have one table with this Column created as an Integer & another as a Varchar. What is the best way to change one