topic SQL UNION operator in SAS Programming

SQL UNION operator

kajal_30 — Thu, 21 Feb 2019 06:35:39 GMT

Hi,

I am trying to join two data sets using proc SQL and both the data sets have different columns is there a way I can make my code work.See example below

PROC SQL;
CREATE TABLE CARS1 AS SELECT
CYLINDERS
,MAKE
,MSRP
,HORSEPOWER
,INVOICE
,LENGTH

FROM SASHELP.CARS
;
QUIT;
PROC SQL;
CREATE TABLE CARS2 AS SELECT
ORIGIN
,TYPE
,WEIGHT
,MAKE
,MSRP
,HORSEPOWER
FROM SASHELP.CARS
;
QUIT;

PROC SQL;
CREATE TABLE CARS3 AS SELECT
MAKE
,MSRP
,HORSEPOWER
,INVOICE
,LENGTH
,CYLINDERS
FROM CARS1
UNION ALL
SELECT
MAKE
,MSRP
,HORSEPOWER
,ORIGIN
,TYPE
,WEIGHT
FROM CARS2
;
QUIT;

ERROR: Column 4 from the first contributor of UNION ALL is not the same type as its counterpart from the second.
ERROR: Column 5 from the first contributor of UNION ALL is not the same type as its counterpart from the second.

I want to use Proc SQL only

thanks

Kajal

Re: SQL UNION operator

Kurt_Bremser — Thu, 21 Feb 2019 06:39:10 GMT

Maxim 14: Use the Right Tool, and that is a data step.

data cars3;
set
  cars1
  cars2
;
run;

Not using it borders on insanity.

Re: SQL UNION operator

Satish_Parida — Thu, 21 Feb 2019 06:55:11 GMT

This works

PROC SQL;
CREATE TABLE CARS1 AS SELECT
CYLINDERS ,MAKE ,MSRP ,HORSEPOWER ,INVOICE ,LENGTH 
FROM SASHELP.CARS
;
QUIT;
PROC SQL;
CREATE TABLE CARS2 AS SELECT
ORIGIN ,TYPE ,WEIGHT ,MAKE ,MSRP ,HORSEPOWER
FROM SASHELP.CARS
;
QUIT;

data cars3;
set cars1;
set cars2;
run;

Please let us know if it worked for you.

Re: SQL UNION operator

RW9 — Thu, 21 Feb 2019 09:55:59 GMT

@Kurt_Bremser has the correct solution (although mostly I would recommend proc append with the force option). To continue using SQL for this you would need to expand both tables to have the same columns:

proc sql;
  create table cars3 as 
  select make
         ,msrp
         ,horsepower
         ,invoice
         ,length
         ,cylinders
         ,""
         ,""
         .
  from   cars1
  union all
  select make
         ,msrp
         ,horsepower
         ,""
         ,.
         ,.
         ,.
         ,origin
         ,type
         ,weight
  from   cars2;
quit;