如何在SAS中直接使用Oracle特有函数--sas Pass-Through Facility

           声明：参考了百度文库中的文档，向有关作者表示感谢！

     在使用sas进行行转列的字符串合并时，发现sas中并没有合适的方法，而oracle在10g之后包含了WMSYS.WM_CONCAT函数，可以轻松的解决这类问题：

     

select t.rank, t.Name from t_menu_item t;

10 CLARK
10 KING
10 MILLER
20 ADAMS
20 FORD
20 JONES
20 SCOTT
20 SMITH
30 ALLEN
30 BLAKE
30 JAMES
30 MARTIN
30 TURNER
30 WARD

--------------------------------
我们通过 10g 所提供的 WMSYS.WM_CONCAT 函数即可以完成 行转列的效果

select t.rank, WMSYS.WM_CONCAT(t.Name) TIME From t_menu_item t GROUP BY t.rank;

DEPTNO ENAME
------ ----------
10 CLARK, KING, MILLER
20 ADAMS, FORD, JONES, SCOTT, SMITH
30 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD

例子如下：

SQL> create table idtable (id number,name varchar2(30));

Table created

SQL> insert into idtable values(10,'ab');

1 row inserted

SQL> insert into idtable values(10,'bc');

1 row inserted

SQL> insert into idtable values(10,'cd');

1 row inserted

SQL> insert into idtable values(20,'hi');

1 row inserted

SQL> insert into idtable values(20,'ij');

1 row inserted
SQL> insert into idtable values(20,'mn');

1 row inserted

SQL> select * from idtable;

ID NAME
---------- ------------------------------
10 ab
10 bc
10 cd
20 hi
20 ij
20 mn

6 rows selected
SQL> select id,wmsys.wm_concat(name) name from idtable
2 group by id;

ID NAME
---------- --------------------------------------------------------------------------------
10 ab,bc,cd
20 hi,ij,mn

SQL> select id,wmsys.wm_concat(name) over (order by id) name from idtable;

ID NAME
---------- --------------------------------------------------------------------------------
10 ab,bc,cd
10 ab,bc,cd
10 ab,bc,cd
20 ab,bc,cd,hi,ij,mn
20 ab,bc,cd,hi,ij,mn
20 ab,bc,cd,hi,ij,mn

6 rows selected

SQL> select id,wmsys.wm_concat(name) over (order by id,name) name from idtable;

ID NAME
---------- --------------------------------------------------------------------------------
10 ab
10 ab,bc
10 ab,bc,cd
20 ab,bc,cd,hi
20 ab,b