SAS计数excel观察 [英] SAS to count excel observations

问题描述

我不需要匹配帐号，只需要匹配帐号文件

 示例文件1：\\directory\Loaded\Jan2014\excel1 
 
示例文件2：\\directory\Loaded\Feb2014\excel2 
 
示例文件3：\\directory\Loaded\Feb2014\excel3 
  

（帐号总是填充在列B中，行1到5作为标题）

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

主文件夹（文件）|子文件夹（Jan2014）|文件名（excel1）|帐号的数量

可以使用SAS吗？

所以如果这还不够，我有搜索网络并找到使用批处理文件的方式来恢复文件列表，但没有任何计数观察值。

解决方案

SAS解决方案就是这样。如果你完成了所有的libnames，然后设置所有的数据集，你可以使它更有效率，但是这个代码有点简单，对于500，我认为这是合理的。不幸的是，excel libnames似乎没有为你做rowcounts，所以你不能只使用dictionary.tables来做到这一点。

如果工作表名称不同，你将需要修改这个以考虑到这一点，要么通过设置一个宏变量来保存表单名称应该是什么，如果它以某种方式链接到文件名，或者通过让宏对dictionary.tables进行查询才能看到什么表中存在libname。

 ％let basedir = c：\temp; *无论您的所有excel文件是上游的基本目录; 
 filename dirl pipedir / b / s& basedir.\ * .xlsx; 
 
 data libnames; 
 infile dirl lrecl = 1024 pad; 
输入
 @ 1文件名$ 1024; 
运行; 
 
％宏get_rowcount（file =）; 
 libname _temp excel& file。 
 data _rowcount; 
 set _temp。Sheet1 $n end = eof; 
 length file_name $ 1024; 
保留file_name& file。 
如果eof然后做; 
 rowcount = _n_; 
输出; 
结束
保持rowcount file_name; 
运行; 
 
 proc append base = rowcounts data = _rowcount force; 
运行; 
％mend get_rowcount; 
 
 proc sql; 
选择cats（'％get_rowcount（file ='，filename，'）'）into：sheetlist用
 from libnames; 
退出; 
& sheetlist; 
  

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: \\directory\Loaded\Jan2014\excel1

Example file 2: \\directory\Loaded\Feb2014\excel2

Example file 3: \\directory\Loaded\Feb2014\excel3

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

Required output using above example:

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

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.

解决方案

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.

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:\temp;  *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屋！