BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SKP
Calcite | Level 5 SKP
Calcite | Level 5

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

@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.

View solution in original post

7 REPLIES 7
SKP
Calcite | Level 5 SKP
Calcite | Level 5

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

ballardw
Super User

@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.

SKP
Calcite | Level 5 SKP
Calcite | Level 5

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

Reeza
Super User

Have you tried PROC APPEND or a data step?

 

data want;

set table1 table2;

run;

SKP
Calcite | Level 5 SKP
Calcite | Level 5

I've tried PROC SQL.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1246 views
  • 0 likes
  • 4 in conversation