在SAS中自动进行表/对象名称扫描和搜索 [英] Automating table/object name scan and search in SAS

问题描述

好的，我先从问题开始: 我每周都会创建产品表，其格式为:

OK I'll start with the problem: I have product tables being created every week which are named in the format:

products_20130701
products_20130708
.
.
.

我正在尝试自动化一些广告系列分析，以便不必每周在代码中手动更改表格名称，就可以使用广告系列最长结束日期之后的第一个产品表格.

I'm trying to automate some campaign analysis so that I don't have to manually change the table name in the code every week to use whichever product table is the first one after the maximum end date of my campaign.

例如

%put &max_enddate.; 
/*20130603*/

我6月份的产品表是:

products_20130602
*products_20130609*
products_20130616
products_20130623

在这种情况下，我想使用列表中的第二个表，忽略价值超过12个月的产品表，只选择日期在我的max_enddate宏之后的表.

in this instance i would like to use the second table in the list, ignoring over 12 months worth of product tables and just selecting the table who's date is just after my max_enddate macro.

我整天都在谷歌搜索，我很沮丧，所以任何建议都将不胜感激.

I've been Googling all day and I'm stumped so ANY advice would be much appreciated.

谢谢！

推荐答案

一种SQL解决方案:

data product_20130603;
run;

data product_20130503;
run;

data product_20130703;
run;

%let campdate=20130601;

proc sql;
  select min(memname) into :datasetname from dictionary.tables 
  where libname='WORK' and upcase(scan(memname,1,'_'))='PRODUCT' and
  input(scan(memname,2,'_'),YYMMDD8.) ge input("&campdate.",YYMMDD8.);
quit;

现在，您可以在set语句中使用& datasetname了，所以

Now you have &datasetname that you can use in the set statement, so

数据my_analysis; 设置&datasetname; (无论您在做什么)； 运行；

    data my_analysis;     set &datasetname;     (whatever you are doing);     run;

将'WORK'修改为适当的libname，如果还有其他限制，则也添加这些限制.如果您有product_somethingnotadate，则可能会收到有关无效日期的一些警告，但这没关系.

Modify 'WORK' to the appropriate libname, and if there are any other restrictions add those as well. You might get some warnings about invalid dates if you have product_somethingnotadate, but that shouldn't matter.

工作方式-dictionary.tables是已访问的所有libname中所有表的列表(与sashelp.vtable相同，但仅在PROC SQL中可用).首先，它会选择名称日期大于或等于广告系列结束日期的所有行；然后从中获取min(memname). Memname当然是一个字符串，但是在除了数字之外相同的字符串中，您仍然可以使用min并获得预期的结果.

The way this works - the dictionary.tables is a list of all tables in all libnames you have accessed (same as sashelp.vtable, but only available in PROC SQL). First this selects all rows that have a name with a date greater than or equal to your campaign end date; then it takes the min(memname) from that. Memname is of course a string, but in strings that are identical except for a number, you can still use min and get the expected result.

这篇关于在SAS中自动进行表/对象名称扫描和搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！