SAS:在宏中从开始到结束日期迭代 [英] SAS: iterate from beginning to end date in a macro

问题描述

我有一个这样的数据集:

DATA tmp;输入标识符 $d0101 d0102 d0103 d0104 d0105 d0106d0107 d0108 d0109 d0110 d0111 d0112;数据线；1 2 3 4 5 6 7 8 9 10 11 12b 4 5 7 4 5 6 7 6 9 10 3 12c 5 2 3 5 5 4 7 8 3 1 1 2;跑步;

我正在尝试创建这样的数据集:

DATA tmp;输入标识符 $ 天值;数据线；'01JAN2018'd 1'02JAN2018'd 2'03JAN2018'd 3'04JAN2018'd 4'05JAN2018'd 5'06JAN2018'd 6'07JAN2018'd 7'08JAN2018'd 8'09JAN2018'd 9'10JAN2018'd 10'11JAN2018'd 11'12JAN2018'd 12b '01JAN2018'd 4b '02JAN2018'd 5b '03JAN2018'd 7...;跑步;

我知道融化"这样的数据集的语法 - 我已经完成了一个类似的宏，用于表示一年中每个十二个月的特定值的列.

我正在努力解决的是如何遍历年初至今的所有天数(假设 have 数据集将 YTD 的所有天数作为列).

我习惯了 Python，所以我可能会在那里做一些事情:

<预><代码>>>>导入日期时间>>>>>>def date_ytd():... end_date = datetime.date.today()... start_date = datetime.date(end_date.year, 1, 1)... diff = (end_date - start_date).days... 对于范围内的 x(0, diff + 1):... yield end_date - datetime.timedelta(days=x)...>>>def create_date_column(dt):... 日，月 = dt.day，dt.month... day_fmt = '{}{}'.format('0' if day <10 else '', day)... month_fmt = '{}{}'.format('0' if month <10 else '', month)...返回 'd{}{}'.format(month_fmt, day_fmt)...>>>结果 = [在dates_ytd() 中为dt 创建_date_column(dt)]>>>>>>结果[:5]['d1031'、'd1030'、'd1029'、'd1028'、'd1027']>>>结果[-5:]['d0105'、'd0104'、'd0103'、'd0102'、'd0101']

这是我的 SAS 尝试:

%MACRO ITER_DATES_YTD();数据_NULL_；%DO v_date = '01012018'd %TO TODAY();%PUT d&v_date.;* 将在这里做熔化"逻辑"；％结尾%MEND ITER_DATES_YTD;

当我使用 %ITER_DATES_YTD(); 运行它时，我的日志甚至没有打印任何内容.我在这里缺少什么?我基本上想遍历YTD"列，例如这些 d0101、d0102、d0103、....

解决方案

这与其说是宏/数据步问题，不如说是一个转置问题.

核心问题是元数据中有数据，这意味着日期"编码在列名中.

示例 1:

转置数据，然后使用 d _name_ 值来计算实际日期.

proc transpose data=have out=have_t(rename=col1=value);通过身份证；跑步;数据想要(保持= id 日期值)；设置 have_t;* 将变量名具有年中元数据的变量名转换为一些常规数据；日期 = 输入 (cats(year(today()),substr(_name_,2)),yymmdd10.);格式化日期 yymmdd10.;跑步;

示例 2:

进行基于数组的转置.D

变量被用作 value_at_date 的角色，并且由于一致的命名约定而易于排列.VNAME 函数从数组引用中提取原始变量名称，并从

部分计算日期值

需要的数据；设置有；数组 value_at_date d:;do index = 1 to dim(value_at_date);date = input(cats(year(today()),substr(VNAME(value_at_date(index)),2)), yymmdd10.);值 = value_at_date(index);输出;结尾;格式化日期 yymmdd10.;保持 id 日期值；跑步;

I have a dataset like this:

DATA tmp;
    INPUT
        identifier $
        d0101 d0102 d0103 d0104 d0105 d0106
        d0107 d0108 d0109 d0110 d0111 d0112
    ;
    DATALINES;
    a 1 2 3 4 5 6 7 8 9 10 11 12
    b 4 5 7 4 5 6 7 6 9 10 3 12
    c 5 2 3 5 5 4 7 8 3 1 1 2
    ;
RUN;

And I'm trying to create a dataset like this:

DATA tmp;
    INPUT
        identifier $ day value
    ;
    DATALINES;
    a '01JAN2018'd 1
    a '02JAN2018'd 2
    a '03JAN2018'd 3
    a '04JAN2018'd 4
    a '05JAN2018'd 5
    a '06JAN2018'd 6
    a '07JAN2018'd 7
    a '08JAN2018'd 8
    a '09JAN2018'd 9
    a '10JAN2018'd 10
    a '11JAN2018'd 11
    a '12JAN2018'd 12
    b '01JAN2018'd 4
    b '02JAN2018'd 5
    b '03JAN2018'd 7
    ...
    ;
RUN;

I know the syntax for "melting" a dataset like this - I have completed a similar macro for columns that represent a particular value in each of the twelve months in a year.

What I'm struggling with is how to iterate through all days year-to-date (the assumption is that the have dataset has all days YTD as columns).

I'm used to Python, so something I might do there would be:

>>> import datetime
>>> 
>>> def dates_ytd():
...     end_date = datetime.date.today()
...     start_date = datetime.date(end_date.year, 1, 1)
...     diff = (end_date - start_date).days
...     for x in range(0, diff + 1):
...         yield end_date - datetime.timedelta(days=x)
... 
>>> def create_date_column(dt):
...     day, month = dt.day, dt.month
...     day_fmt = '{}{}'.format('0' if day < 10 else '', day)
...     month_fmt = '{}{}'.format('0' if month < 10 else '', month)
...     return 'd{}{}'.format(month_fmt, day_fmt)
... 
>>> result = [create_date_column(dt) for dt in dates_ytd()]
>>> 
>>> result[:5]
['d1031', 'd1030', 'd1029', 'd1028', 'd1027']
>>> result[-5:]
['d0105', 'd0104', 'd0103', 'd0102', 'd0101']

Here is my SAS attempt:

%MACRO ITER_DATES_YTD();
    DATA _NULL_;
        %DO v_date = '01012018'd %TO TODAY();
            %PUT d&v_date.;
            * Will do "melting" logic here";
        %END
%MEND ITER_DATES_YTD;

When I run this, using %ITER_DATES_YTD();, nothing is even printed to my log. What am I missing here? I basically want to iterate through "YTD" columns, like these d0101, d0102, d0103, ....

解决方案

This is more a transposition problem than a macro / data step problem.

The core problem is that you have data in the metadata, meaning the 'date' is encoded in the column names.

Example 1:

Transpose the data, then use the d<yymm> _name_ values to compute an actual date.

proc transpose data=have out=have_t(rename=col1=value);
  by id;
run;

data want (keep=id date value);
  set have_t;

  * convert the variable name has day-in-year metadata into some regular data;
  date = input (cats(year(today()),substr(_name_,2)),yymmdd10.);

  format date yymmdd10.;
run;

Example 2:

Do an array based transposition. The D<mm><dd> variables are being used in a role of value_at_date, and are easily arrayed due to a consistent naming convention. The VNAME function extricates the original variable name from the array reference and computes a date value from the <mm><dd> portion

data want;
  set have;
  array value_at_date d:;

  do index = 1 to dim(value_at_date);
    date = input(cats(year(today()),substr(VNAME(value_at_date(index)),2)), yymmdd10.);
    value = value_at_date(index);
    output;
  end;

  format date yymmdd10.;
  keep id date value;
run;

这篇关于SAS:在宏中从开始到结束日期迭代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！