SAS 计算 excel 观察值 [英] SAS to count excel observations

问题描述

我必须对大约 500 个 excel 文件的一些 excel 文件进行账目核对.

I have to carry out a reconciliation of accounts for a number of excel files approximately 500 excel files.

我不需要按文件只匹配帐号.

I don’t need to match accounts numbers just the volume by file.

Example file 1: \directoryLoadedJan2014excel1

Example file 2: \directoryLoadedFeb2014excel2

Example file 3: \directoryLoadedFeb2014excel3

(帐号始终填写在 B 列中，第 1 到 5 行作为标题)

(account number is always populated in column B with rows 1 to 5 as the titles)

使用上述示例所需的输出:

Required output using above example:

主文件夹(文件)|子文件夹 (Jan2014) |文件名 (excel1) |帐号计数

Main Folder (File) | Sub Folder (Jan2014) | File Name (excel1) | count of account numbers

这可以使用 SAS 吗?

Is this possible using SAS?

因此，如果这还没有足够的信息，我已经搜索了网络并找到了使用批处理文件带回文件列表但没有任何观察结果的方法.

So if this is not enough information, i have searched the net and found ways using batch files to bring back a list of files but nothing that counts the observations.

推荐答案

SAS 解决方案是这样的.如果你做了所有的 libnames 然后设置所有的数据集，你可以让它更有效率，但是这段代码更容易一些，对于 500 我认为这是合理的.不幸的是，excel libnames 似乎不会为您计算行数，因此您不能只使用 dictionary.tables 来执行此操作.

The SAS solution is something like this. You could make this a bit more efficient if you did all of the libnames and then set all of the datasets, but this code is a bit easier, and for 500 I think it's reasonable. Unfortunately excel libnames don't seem to do rowcounts for you, so you can't just use dictionary.tables to do this.

如果工作表名称不同，您需要修改它以考虑到这一点，方法是设置一个宏变量来保存工作表名称(如果它以某种方式链接到文件名)，或者让宏查询 dictionary.tables 以查看 libname 中存在哪些表.

If the sheet names vary, you will need to modify this to take that into account, either by setting up a macro variable that holds what the sheet name should be if it's linked to the filename in some way, or by having the macro do a query to dictionary.tables to see what tables are present in the libname.

%let basedir=c:	emp;  *whatever the base directory is that all of your excel files are upstream from;
filename dirl pipe "dir /b/s &basedir.*.xlsx";

data libnames;
infile dirl lrecl=1024 pad;
input
@1 filename $1024.;
run;

%macro get_rowcount(file=);
  libname _temp excel "&file.";
  data _rowcount;
  set _temp."Sheet1$"n end=eof;
  length file_name $1024;
  retain file_name "&file.";
  if eof then do;
    rowcount=_n_;
    output;
  end;
  keep rowcount file_name;
  run;

  proc append base=rowcounts data=_rowcount force;
  run;
%mend get_rowcount;

proc sql;
select cats('%get_rowcount(file=',filename,')') into :sheetlist separated by ' '
  from libnames;
quit;
&sheetlist.;

这篇关于SAS 计算 excel 观察值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！