SAS中文论坛

标题: 非常规跨表查询,求助. [打印本页]

作者: shiyiming 时间: 2013-4-10 15:43
标题: 非常规跨表查询,求助.
两个表city, customer

[b:3czjas30]CITY[/b:3czjas30]
No CITY
1 SHANGHAI
2 BEIJING
3 SHENZHEN
4 GUANGZHOU
5 HANGZHOU

[b:3czjas30]CUSTOMER[/b:3czjas30]
Cust_id Address
1 #3523420 SHANGHAI
2 234hkjhad asdf SHANGHAI
3 #4390523 dfklj GUANGZHOU asdfkasdf
4 #435 HANGZHOU 34523
5 #345934 dsfkjlg

现在的问题是,我要再customer表里面增加一个新的列,城市.
也就是说扫描address,然后如果里面的值和城市表的城市值一样,就提取这个城市的名字.

求助...

作者: shiyiming 时间: 2013-6-1 09:05
标题: Re: 非常规跨表查询,求助.
[code:2j9epq8g]proc sql noprint;
  select count(*) into :cnt
  from city;
quit;

data _null_;
call symput ('cnt', strip(put(&cnt0,8.)));
run;

proc sql noprint;
  select city into :var1 - :var&cnt
  from city;
quit;

%macro mylist;
%do i=1 %to &cnt;
"&&var&i"
%end;
%mend;

data NEW_CUSTOMER;
length CITY $20.;
  set CUSTOMER;
  array test[&cnt] $20  _TEMPORARY_ (%mylist) ;
  do i=1 to &cnt;
  if index(address,strip(test(i)))>0 then city=test(i);
  end;
  drop i;
run;[/code:2j9epq8g]

欢迎光临 SAS中文论坛 (http://www.mysas.net/forum/)