Hi,
I'm trying to append data from one table to the other and create new table with UNION ALL query, but got the error which seems to be due to NULL value in one of the field.
Can someone please let me know how I should do this?
Here is what I am trying to do:
In table 1, I have
Customer ID
Cutomer Name
Location
Product ID
Purchase amount in Jan
Purchase amount in Feb
Purchase amount in March
Purchase amount in April
In table 2, I have
Cutomer Name
Product ID
Purchase amount in Jan
Purchase amount in Feb
Purchase amount in March
Purchase amount in April
I'm trying to append data in table 2 to table 1, and rename it as C.
Thank you for your help.
SKP
@SKP wrote:
Hi Kurt,
I have query as:
Proc Sql;
Create table C as
Select * from table 1
Union
Select * from table 2;
quit;
Error log says:
Colum 1 from the first contributer of UNION is not the same type as its counterpart from the second
Thanks,
SKP
SAS requires variables to be of the same type when combining data in a column. So whatever is in the first column of TABLE 1 is not the same type as Table2. Either both character or both numeric.
And your syntax won't run as you have spaces between table and the 1 or 2.
Proc contents on your data sets will tell you what variables are in each position and their data type.
It helps when you have a error to COPY the code and any messages from the log and PASTE into a code box opened with the {i} forum icon. Error messages are sometimes position sensitive and the forum windows will alter the formatting from as displayed in the log window.
Please post the log and run a proc contents on both datasets, and post the output.
Hi Kurt,
I have query as:
Proc Sql;
Create table C as
Select * from table 1
Union
Select * from table 2;
quit;
Error log says:
Colum 1 from the first contributer of UNION is not the same type as its counterpart from the second
Thanks,
SKP
@SKP wrote:
Hi Kurt,
I have query as:
Proc Sql;
Create table C as
Select * from table 1
Union
Select * from table 2;
quit;
Error log says:
Colum 1 from the first contributer of UNION is not the same type as its counterpart from the second
Thanks,
SKP
SAS requires variables to be of the same type when combining data in a column. So whatever is in the first column of TABLE 1 is not the same type as Table2. Either both character or both numeric.
And your syntax won't run as you have spaces between table and the 1 or 2.
Proc contents on your data sets will tell you what variables are in each position and their data type.
It helps when you have a error to COPY the code and any messages from the log and PASTE into a code box opened with the {i} forum icon. Error messages are sometimes position sensitive and the forum windows will alter the formatting from as displayed in the log window.
ballardw
Thank you for your comment
I think I know what's wrong with my query now. I'll try to fix and post another question if it's doen't work,
SKP
Have you tried PROC APPEND or a data step?
data want;
set table1 table2;
run;
I've tried PROC SQL.
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.