[讨论]SAS PROC SQL

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

When merging two tables, you can say merge a(in=ina) b(in=inb) ; and check to determine if values are coming in from table a and b. Is there something similar in PROC SQL when joining two tables?

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏3 回帖

关键词：proc sql ROC sql something determine 讨论 SAS proc sql

应该可以对指定字段用case语句进行替换

如case a when '' 替换为表中另一字段

when 'ss'

else

记得我用过一个不同的做法，等我找一下！

proc sql ;

create table ab as

select

*

from a ina ,b inb

where ina.unikey=ina.unikey;

quit;

[此贴子已经被作者于2007-2-9 20:21:19编辑过]

that sql is the same as following code.

data ab;

merge a (in = ina )

b (in = inb );

by unikey ;

if a and b ;

run;

You have two ways to do it:

1. Use 'case' function to create a new variable, 'case' is really similar like 'if ... then' in your datastep.

2. Use 'coalesce' with OUTER JOINS (full join, left join, right join) operations:

coalesce (a.id, 0) will change all missing values or unmatched values of a.id as 0.

coalesce (b.id, 9999) will change all unmatched or missing value of b.id as 9999.

data a;

input id age;

cards;

3 12

4 5

5 35

6 20

7 11

8 20

;

run;

data b;

input id gender $;

cards;

1 F

2 M

4 M

6 F

7 M

10 F

11 M

;

run;

***METHOD 1****;

proc sql;

select a.*, b.gender,

case when a.id>0 then 1 else 0 end as ina,

case when b.id>0 then 1 else 0 end as inb

from a full join b

on a.id=b.id;

quit;

***MATHOD 2****;

proc sql;

select a.*, b.gender, coalesce(a.id, 0) as ina, coalesce (b.id,9999) as inb

from a full join b

on a.id=b.id;

quit;

[此贴子已经被作者于2007-2-23 5:33:31编辑过]

1) Full join is the same as:

data new;

merge a (in=ina) b(in=inb);

by id;

if ina or inb;

run;

proc sql;

create table new as

select *

from a full join b

on a.id=b.id;

quit;

2) LEFT JOIN is the same as:

data new;

merge a (in=ina) b(in=inb);

by id;

if ina ;

run;

proc sql;

create table new as

select *

from a left join b

on a.id=b.id;

quit;

3) RIGHT JOIN is the same as:

data new;

merge a (in=ina) b(in=inb);

by id;

if inb;

run;

proc sql;

create table new as

select *

from a right join b

on a.id=b.id;

quit;