非常规跨表查询,求助.

shiyiming

两个表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

[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]