在 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 月份的产品表是:

my product tables in June are:

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' 修改为适当的库名，如果有任何其他限制，也添加这些限制.如果您有 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 是您访问过的所有库名中所有表的列表(与 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屋！