%do loop to create variables for dates between two dates

[aboloori]

I have two dates (start and stop), and I'm trying to create one column for each date between these two dates. Below is the summary of a macro I'm using. However, I'm getting this error: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. I was wondering if anyone could help me with this error. Thanks in advance!

data have;
INFORMAT date_start date_stop MMDDYY10.;
input date_start date_stop ;
FORMAT date_start date_stop MMDDYY10.;
datalines;
1/15/2008 1/16/2008
1/28/2008 1/30/2008
2/12/2008 2/16/2008
;

data want;
INFORMAT date_start date_stop date_1 date_2 date_3 date_4 date_5 MMDDYY10.;
input date_start date_stop date_1 date_2 date_3 date_4 date_5;
FORMAT date_start date_stop date_1 date_2 date_3 date_4 date_5 MMDDYY10.;
datalines;
1/15/2008 1/16/2008 1/15/2008 1/16/2008
1/28/2008 1/30/2008 1/28/2008 1/29/2008 1/30/2008
2/12/2008 2/16/2008 2/12/2008 2/13/2008 2/14/2008 2/15/2008 2/16/2008
;

/* SAS macro code */

%macro test;
data want;
set have;
%do i = 1 %to date_stop - date_start + 1;
format date&i MMDDYY10.;
date&i = intnx('day', date_start, i);
%end;
run;
%mend;
%test

[Reeza]

I would not recommend a macro. Use a long data set (you may want to keep your data in this format, easier to work with typically) and then use transpose.

data have;
    INFORMAT date_start date_stop MMDDYY10.;
    input date_start date_stop;
    FORMAT date_start date_stop MMDDYY10.;
    datalines;
1/15/2008 1/16/2008
1/28/2008 1/30/2008
2/12/2008 2/16/2008
;

data long;
    set have;
    counter=0;

    do date=date_start to date_stop;
        counter+1;
        output;
    end;
run;

proc transpose data=long out=want prefix=date;
    by date_start date_stop;
    id counter;
    var date;
    format date: date9.;
run;

[Reeza]

And the macro language can't really access a variable value in the way you're using it in the %DO statement which is what generates the error.

[Patrick · Accepted solution]

Not sure why you need a macro for this task. 

Below datastep code only. I'd go for a long table structure (want_long). If you choose a wide structure then you need to either pre-process your data to determine in advance how many array elements you need or you need to define a number that's certainly higher than what you ever would need.

data want_long;
  set have;
  format date_want date9.;
  do date_want=date_start to date_stop;
    output;
  end;
run;

data want_wide(drop=_:);
  set have;
  array date_ {10};
  format date_: date9.;
  do _date=date_start to date_stop;
    date_[_date-date_start+1]=_date;
  end;
run;

[Tom]

This line makes no sense

%do i = 1 %to date_stop - date_start + 1;

The upper bound (the thing after the %TO) has to be a number.  So the macro processor called the %EVAL() function to convert the text you typed into a number.  But DATE_STOP and DATE_START are not numbers but long character strings.  So the %EVAL() could not find any number to calculate.

Are you sure you didn't mean to write an actual DO statement?

do i = 1 to date_stop - date_start + 1;

That would at least  make sense because to the data step the text string DATE_STOP is the name of a variable in the dataset being created.

To reference a variable using an index value, like your I variable, you need to use an ARRAY.

If you know you only want to make 5 new variables then just define the array that large.

data want;
  set have;
  array days date1 - date5;
  format date1-date5 yymmdd10.;
  do i=1 to max(5,date_stop - date_start + 1);
       days[i] = date_start + i -1;
  end;
run;

Note: There no need to use INTNX() to increment date/time/datetime values by the units they are stored in.  Just increment by integer values.

If you don't have some upper bound on the number of variables you want to create then why make sure a difficult to use structure?  Why not just add only ONE new variable?

data want;
  set have;
  do date=date_start to date_stop;
    output;
  end;
run;

[Amir]

Hi,

The following (format statement borrowed from @Patrick) identifies the maximum date range before making use of it in the final data step logic.

/* find the maximum date range that will be required */
data _null_;
  do until(last_obs);
    set have end = last_obs;
    max_range = max(max_range,date_stop-date_start+1);
  end;
  
  call symputx('max_range',max_range);
run;

%put &=max_range;

data want(drop = i);
  set have;

  array date_ [&max_range];
  format date_: MMDDYY10.;
  
  do i = date_start to date_stop;
    date_[i-date_start+1] = i;
  end;
run;

Giving the output:

Thanks & kind regards,

Amir.