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
@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;
Maxim 14: Use the Right Tool, and that is a data step.
data cars3;
set
cars1
cars2
;
run;
Not using it borders on insanity.
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.
@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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.