SAS: iterate from beginning to end date in a macro

问题:

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, ....

回答1:

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;

回答2:

To iterate through dates, you have to convert it to numbers first and then extract date part from it.

%macro iterateDates();
    data _null_;
        %do i = %sysFunc(inputN(01012018,ddmmyy8.)) %to %sysFunc(today()) %by 1;
            %put d%sysFunc(putN(&i, ddmmyy4.));
        %end;
    run;
%mend iterateDates;

%iterateDates();

I think that '01012018'd is processed only in data step, but not in the macro code. And keep in mind, that macro code is executed first and only then the data step is executed. You can think about it like building SAS code with SAS macros and then running it.