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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.