01 7月 17 何种方式最有效地得到数据表的观测数?

何种方式最有效地得到数据表的观测数?
reproduced by sxlion

导读：SASor论坛上经典对话很多，以前用爬虫把整个网站down下来过一次，可惜没时间整理，然后电脑升级，最后就丢失了。每每想重温论坛，都无法得到原文。网络上仅仅转载了部分的对话，想起来真是可惜。现在仅凭一些时有时无的记忆点，在网络重搜出来一些经典对话，整理分享一下【论坛沉钩001】。

QKAIWEI:
1

1
2
3

%let rc=%sysfunc(open(yourTable));
%let nobs=%sysfunc(attrn(&rc,nobs));
%let rc=%sysfunc(close(&rc));

1
2
3

proc sql noprint;
select count(1) into:nobs from yourTable;
quit;

1
2
3
4
5
6

data _null_;
point=0;
set yourTable point=point nobs=n;
call symput('nobs',n);
stop;
run;

others???

SAS_DREAM:

1
2
3

%let rc=%sysfunc(open(yourTable));
%let nobs=%sysfunc(attrn(&rc,nobs));
%let rc=%sysfunc(close(&rc));

– open, attrn是SCL函数在base里的调用方式，需要测试一下对AF license的依赖性，该方法从表头meta区读取信息，与数据大小无关，效率较高。不过attrn要建立scl list，稍费周折。

1
2
3

proc sql noprint;
select count(1) into:nobs from yourTable;
quit;

– 顺序遍历表，数数记录，不从表头取信息，表越大，时间越长。

1
2
3
4
5
6

data _null_;
point=0;
set yourTable point=point nobs=n;
call symput('nobs',n);
stop;
run;

– 考虑一下空表情形：

1
2
3
4
5
6

%let nobs = 0;
data _null_;
set yourTable (drop=_all_) nobs=n;
call symput('nobs',n);
stop;
run;

如果空表，n是取不到值的，所以宏obs要赋初值。
(drop=_all_)当表列数很多又是通过远程提交时，这个选项会省时间。
point=0不用也可，因为stop会限制data step在一条记录以后就停。

这个方法也是直接从表头meta区取观测数，与表大小无关。

4 others???
你已经知道这么多方法，除了第二条没有什么理由去用，第一，三方法都是既简洁准确又不用一秒钟就精确搞定，余下的时间，不如去想其它有意思的专题了。

IMOEN:

1
2
3
4
5

PROC SQL NOPRINT;
SELECT NOBS INTO: NUM_OF_ROW
FROM SASHELP.VTABLE
WHERE LIBNAME="SASHELP" AND MEMNAME="CLASS";
QUIT;%PUT &NUM_OF_ROW;

这种方法也是直读metadata，优点是不仅仅可以用来读出纪录数，还可读出大量有用的表信息。打开sashelp.vtable看一看就知道了。

SAS_DREAM:

imoen 写到:

1
2
3
4
5

PROC SQL NOPRINT;
SELECT NOBS INTO: NUM_OF_ROW
FROM SASHELP.VTABLE
WHERE LIBNAME="SASHELP" AND MEMNAME="CLASS";
QUIT;%PUT &NUM_OF_ROW;

这种方法也是直读metadata，优点是不仅仅可以用来读出纪录数，还可读出大量有用的表信息。打开sashelp.vtable看一看就知道了。

透过vtable这样一个集中元数据表来访问表信息，对于一次性访问多个表的多种信息，是很方便的。

但是就每次零星地考察单表记录数而言，有些地方似乎值得进一步考量：
sashelp.vtable是sashelp库里的一个sql view, 应该是有后台管理进程辅助更新。对于通过vtable访问某个表的记录数，可能有几个担心：
1，在vtable中搜寻一个单表的信息需要进行View的retrieve遍历，还要进行逐条比对（WHERE LIBNAME=”SASHELP” AND MEMNAME=”CLASS”）；尚不清楚vtable view的生成代码是什么(感觉像是Proc contents类似的操作生成的)，但是该view的建立应该很难高效地利用针对“libname”和”memname”的索引，这样，搜寻访问效率可能随着view retrieve遍历的表记录增加而降低。
当系统中总的表数增大到一个较大的数量级，这在大型数据仓库系统，尤其是系统安装的模块较多，或者存在大量OLAP, EM projects时很有可能发生，vtable中包含的内容就会很多，因为它的内容涵盖系统中所有的表，甚至临时表；此时如果又用不上索引，vtable的访问速度会相当慢，事实上，用VT sashelp.vtable时已会有所感觉。
2，对于服务器端的应用，如果多个连接程序需要同时访问vtable取信息时，可能会进一步面临效率问题
3，当服务器端系统中一方面不断生成和删除大量表，同时有很多远程连接需要访问vtable取信息时，vtable所透视的内容可能面临更新和取用的双重压力

不过对于一般应用情形，都是OK了。上面的担心只是瞎猜而已… …

IMOEN:

View只在被访问时才生成数据。同时，SAS的串行查询，令到冲突几乎不可能发生。

由于都是访问metadata，即使系统中有数千个表，也是秒级的操作。在大数据量ETL优化时，这类秒级操作基本上是忽略的。程序的可读性和通用性更加重要。

在主流DBMS中，SQL优化器在遇到qkaiwei第二种方法时，都会直接查询meta，而不是遍历原数据。如果数据引擎用的是其他DBMS，最快的方法是用SQL Pass-through。例如：

1
2
3
4
5
6

Proc SQL;
CONNECT TO ODBC AS DW ();SELECT nob INTO :NO_OF_ROW
FROM CONNECTION TO DW
(SELECT count(*) AS nob
FROM a_table);DISCONNECT FROM DW;
QUIT;

如果在SAS里面Count(*)，立刻就会把所有的数据，通过你的接口引擎，在SAS中遍历。两种方法的速度有本质的差别。

SAS_DREAM:

imoen 写到:

1
2
3
4
5
6

Proc SQL;
CONNECT TO ODBC AS DW ();SELECT nob INTO :NO_OF_ROW
FROM CONNECTION TO DW
(SELECT count(*) AS nob
FROM a_table);DISCONNECT FROM DW;
QUIT;

如果在SAS里面Count(*)，立刻就会把所有的数据，通过你的接口引擎，在SAS中遍历。两种方法的速度有本质的差别。

说得有道理！

感谢Q版，大清早信手拈来就给大家一个好玩的话题！
今天和vtable玩了个游戏，发现一些现象很有趣：

1，先造10000个表在系统里

1
2
3
4
5
6
7
8
9
10
11
12
13

%let tableNum = 10000;

%macro tables();
data
%do i=1 %to &tableNum;
test&i
%end;
;
set sashelp.class;
run;
%mend tables;

%tables;

2，再去找其中TEST100的观测数
set nobs跑了1秒

1
2
3
4
5
6
7
8
9

%let nobs=0;

data _null_;
set TEST100(drop=_all_) nobs=rows;
call symput('nobs',rows);
stop;
run;

%put &nobs;

NOTE: There were 1 observations read from the data set WORK.TEST100.
NOTE: DATA statement used:
real time 1.00 seconds
cpu time 0.00 seconds

FROM SASHELP.VTABLE跑了29秒

1
2
3
4
5

PROC SQL;
SELECT NOBS
FROM SASHELP.VTABLE
WHERE LIBNAME="WORK" AND MEMNAME="TEST100";
QUIT;

NOTE: PROCEDURE SQL used:
real time 29.10 seconds
cpu time 0.17 seconds

如果再运行一遍，由于cache的机理，两个方法都是不到1秒了。

猜想：
vtable被访问时的应该是动态收集系统中所有的表meta，搜集的耗时与表的数目有关，而且不论是访问那个表的信息（where 。。。），都是先去收集系统中所有的表meta；否则，利用WHERE LIBNAME=”WORK” AND MEMNAME=”TEST100″取一条记录，不会在系统表数目增长时产生显著的响应下降（系统表1000个时，就是2，3秒而已）
而set nobs的方法与系统表数目无关。
因此，极端情况下vtable还是有响应效率问题的，不过算不了什么。

结论：
1万个系统表的状况还是比较罕见的，而且vtable的响应也是可以接受（机器快了更是影响比较小），如Imoen所说，使用vtable方法比较通用和易读，也不致在效率上产生实质问题。
只取观测数的话，set nobs方法仍是比较高效的。
如果愿意，编个macro把set nobs和vtable的优势结合，也是很好玩的。

提示：
这个测试情形必须保证消除cache效应，方法可以是在每种方法测试之前，运行一个大进大出的summary，搞到out of memory更好，会把之前测试的cache清得一干二净，各种方法的真实效率才会显现，在其它类似测试中也如是。

QKAIWEI:
我其实一直想努力使sasor向这个方向走：画一个圈，挖一口井。

我一般使用data步的做法，比较省时，而且sas语言上data步是最稳定的，如果用别的，说不定哪里就来个bug。

如果是在scl中，我一般用
dsid=open(talbename);
nobs=attrn(dsid,’nobs’);
…
接下来，我可能要前移游标，做变量取值操作。但是scl语法不能操作sas server上的表，使用%sysfunc一般也是折衷之计。

在远程sas server如果想得到scl的同样效果，我的做法是

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

rsubmit;
%let rc=%sysfunc(open(tablename));
%let nobs=%sysfunc(attrn(&rc,nobs));
%let rc=%sysfunc(close(&rc));

data _null_;
nobs=&nobs;
do p=1 to nobs
set my table point=p;
call symput('a',a);
call symput('b',b);
call execute('%macro1(&a)');
call execute('%macro2(&b)');
end;
stop;run;
endrsubmit;

proc sql在使用v系列的视图确实相比其他方法比较耗费时间，当然这个差别要考虑机器性能。
我以前的电脑在使用这一方法的时候总要顿一下。
当然imoen的说的也是，就是在读取其他数据库的时候，如oracle上的表时，这是scl 以及data步不能实现的。

在sas server上分组取观测数时，proc sql也很有意思，有的时候也用一下，比较方便。

1
2
3
4
5
6

rsubmit;
proc sql noprint;
select count(1) into:group_obs separated by ',' from tblname group by var;
quit;
&sysrput group_obs=&group_obs /*caution the length of macro variable*/
endrsubmit;

接下来有个%superq的用法，然后循环，用%scan分别取值。
我去做饭，不能多写了，希望多开一些这样的题目，做深入的讨论。
两位周末快乐！

原创文章: ”何种方式最有效地得到数据表的观测数?“，转载请注明：转自SAS资源资讯列表

本文链接地址: http://saslist.net/archives/448