Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- proc sql : Outer union corr

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

01-14-2010 12:32 PM

I need to concatenate two data sets using outer union corr. But these two datasets have different number of variables

WORK.READXTRAN has 196955 observations and 264 variables

WORK.READXTRAO has 1012170 observations and 245 variables

I'm trying to do:

proc sql;

Create table readxtra as

select *

from readxtraN

OUTER UNION CORRESPONDING

select *

from readxtraO;

quit;

I get an error: ERROR: Column 203 from the first contributor of OUTER UNION is not the same type as its counterpart

Please suggest how I should contenate these two datasets using proc sql?

WORK.READXTRAN has 196955 observations and 264 variables

WORK.READXTRAO has 1012170 observations and 245 variables

I'm trying to do:

proc sql;

Create table readxtra as

select *

from readxtraN

OUTER UNION CORRESPONDING

select *

from readxtraO;

quit;

I get an error: ERROR: Column 203 from the first contributor of OUTER UNION is not the same type as its counterpart

Please suggest how I should contenate these two datasets using proc sql?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

01-16-2010 08:00 AM

you'll need to rename that column, and any others which have different datatypes for the same name.

The message indicates its number not its name, but PROC CONTENTS will provide the numbers.

The message indicates its number not its name, but PROC CONTENTS will provide the numbers.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-24-2016 09:37 AM

Can someone elaborate on this? I am having the same issue, but I don't understand the solution here.

What do you mean rename the column? Just name the variable name?

I have a number of these errors! Does that mean I have to rename every single column? What do I rename them to?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-25-2016 02:35 AM

Run below code. That will hopefully explain it to you.

```
data have1;
attrib
A length=8
b length=8;
A=1;
B=1;
run;
data have2;
attrib
A length= 8
B length= $20;
A=2;
B='2';
run;
/* throws an error because column B is of different type in source tables */
/* error indicates which column based on variable number causes the issue */
/* running proc contents over source tables will return the variable names with attributs */
/* so the problematic column is easily identified */
proc sql;
create table demo1 as
select *
from have1
outer union corresponding
select *
from have2
;
quit;
/* will work */
proc sql;
create table demo1 as
select a, b as b1
from have1
outer union corresponding
select a, b as b2
from have2
;
quit;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-26-2016 12:59 PM

Thanks Patrick for taking the time to post the example. It really does help!

I have a question though.

I notice that the correct proc sql code renames variables b to b1 from dataset have 1, and then renames b to b2 from dataset 2. But for the merging of the two datasets I want b1 and b2 to be in the same column.

So for example:

data set have 1 has variables a and b

data set have 2 has variables a and b

final merged data set has variables a and b which contains the data points from variables a and b from dataset have 1 and have2.

is there a way to do this?

Thank you