n********6
发帖数: 1511 | 1 tableA
id name salary06 ...
1 mike 1000
2 john 2000
4 tom 3000
tableB
id name income06 income07 ...
1 mike 1000 ...
2 john 3000
3 smith 3000
5 Leo 5000
Objective:
- Combine all the records from two tables into tableC(id, name, income)
- For a specific id, if B.income exists, use that for C.income.
- For a specific id, If B.income does not exist, use A.salary for C.income
Expected outcome tableC
id name income06 income07 ...
1 mike 1000 ...
2 john 3000
3 smith | B*****g
发帖数: 34098 | 2 I don't believe SAS invent merge.
【在 n********6 的大作中提到】
: tableA
: id name salary06 ...
: 1 mike 1000
: 2 john 2000
: 4 tom 3000
: tableB
: id name income06 income07 ...
: 1 mike 1000 ...
: 2 john 3000
: 3 smith 3000
| j*****n
发帖数: 1781 | 3 Here is the core part in T-SQL, you finish the rest...
SELECT C.id, C.name, A.salary, B.income
FROM (
SELECT id, name
FROM tableA
UNION
SELECT id, name
FROM tableB
) C
LEFT JOIN tableA ON C.id = A.id
LEFT JOIN tableB ON C.id = B.id
【在 n********6 的大作中提到】
: tableA
: id name salary06 ...
: 1 mike 1000
: 2 john 2000
: 4 tom 3000
: tableB
: id name income06 income07 ...
: 1 mike 1000 ...
: 2 john 3000
: 3 smith 3000
| B*****g
发帖数: 34098 | 4 你需要升级了
【在 j*****n 的大作中提到】
: Here is the core part in T-SQL, you finish the rest...
: SELECT C.id, C.name, A.salary, B.income
: FROM (
: SELECT id, name
: FROM tableA
: UNION
: SELECT id, name
: FROM tableB
: ) C
: LEFT JOIN tableA ON C.id = A.id
| j*****n
发帖数: 1781 | 5 CTE SAS 用不了吧？
【在 B*****g 的大作中提到】
: 你需要升级了
| B*****g
发帖数: 34098 | 6 google "sql server 2008 merge"
【在 j*****n 的大作中提到】
: CTE SAS 用不了吧？
| j*****n
发帖数: 1781 | 7 Damn, i just need to retire....
【在 B*****g 的大作中提到】
: google "sql server 2008 merge"
| o******5
发帖数: 29 | 8 Here is the Oracle code:
create table c
as
select nvl(a.id,b.id) id,nvl(a.name,b.name) name,nvl(b.income06,a.salary06)
income06
from a full outer join b
on a.id=b.id
order by 1;
I do not know whether you can use PROC SQL to achieve this same thing or not
. | B*****g
发帖数: 34098 | 9 oracle merge
)
not
【在 o******5 的大作中提到】
: Here is the Oracle code:
: create table c
: as
: select nvl(a.id,b.id) id,nvl(a.name,b.name) name,nvl(b.income06,a.salary06)
: income06
: from a full outer join b
: on a.id=b.id
: order by 1;
: I do not know whether you can use PROC SQL to achieve this same thing or not
: .
| o******5
发帖数: 29 | 10 I gave it a little more thought on this. In fact, you can use PROC SQL to do
this.
proc sql;
create table c
as
select coalese(a.id,b.id) as id,coalese(a.name,b.name) as name,coalese(b.
income06,a.salary06) as income06
from a full outer join b
on a.id=b.id
order by 1;
quit; |
|
