Macro programming - use variable value for stop value in do loop

[robulon]

Hi, me again!

I want to run a macro program in Enterprise Guide with a do loop in it that will populate a certain number of date variables.

The original data set contains from date1 to date7 but I want it to go up to date12, based on the value of the instalments variable in each observation.

My code is: -

%macro test;

data test_2;

set test;

format promise_1_date8 promise_1_date9 promise_1_date10 promise_1_date11 promise_1_date12 ddmmyy10.;

%if instalments > 7 %then %do a = 8 %to instalments;

promise_1_date&a. = intnx('month',promise_1_date2,&a.,'same');

%end;

run;

%mend test;

%test

Which returns the following errors: -

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:

instalments

ERROR: The %TO value of the %DO A loop is invalid.

ERROR: The macro TEST will stop executing

Is it possible to use the value of instalments for each observation to populate only that number of dates (leaving the additional date variables  blank?

At the moment I am using %do a=8 %to 12, and then using further if then do statements to reset the unneeded dates to missing but I'm assuming there is a smarter way to do this?

As always, thanks in advance for any assistance,

Rob

[Astounding]

Based on what you have described, the program should use base SAS only.  You might need to add an ARRAY statement.  But you would not need macro language at all.

[robulon]

Thanks.

I've never really used arrays but have given it a go as follows.

data test_2;

set prom_201812;

format promise_1_date8 promise_1_date9 promise_1_date10 promise_1_date11 promise_1_date12 ddmmyy10.;

array pdate [5] promise_1_date8 - promise_1_date12;

array pamt [5] promise_1_amt8 - promise_1_amt12;

if instalments > 7 then do i = 8 to 12;

pdate[i] = intnx('month',promise_1_date2,[i],'same');

pamt[i] = promise_1_amt2;

end;

run;

The problem now is that the array doesn't seem to like me having a constant variable value (promise_1_date2) as the reference point from which to calculate the other dates, as I get this in the log: -

39 if instalments > 7 then do i = 8 to 12;

40 pdate[i] = intnx('month',promise_1_date2,[i],'same');

_

22

ERROR: Undeclared array referenced: NAME.

ERROR: Variable NAME has not been declared as an array.

ERROR 22-322: Syntax error, expecting one of the following: a name, INPUT, PUT.

[Tom]

Why do you have square brackets around the offset value in the INTNX function call?

That is just going to cause trouble.

666   data _null_;
667     x = mean([1],2,3);
                 -
                 22
ERROR: Undeclared array referenced: NAME.
ERROR: Variable NAME has not been declared as an array.
ERROR 22-322: Syntax error, expecting one of the following: a name, INPUT, PUT.

668   run;

[Astounding]

@Tom hit the nail on the head here.  Modify this statement:

pdate[i] = intnx('month',promise_1_date2,[i],'same');

Remove the square brackets within the INTNX function (not anywhere else)::

pdate[i] = intnx('month',promise_1_date2, i ,'same');

And, of course, verify that the numbers that you get are correct.

[robulon]

Thanks both, really appreciate you taking the time to assist me.

Having taken the square brackets off the i in the intnx statement, I'm now getting an error telling me the array subscript is out of range?

[Tom]

---

@robulon wrote:

Thanks both, really appreciate you taking the time to assist me.

Having taken the square brackets off the i in the intnx statement, I'm now getting an error telling me the array subscript is out of range?

---

Why did you code DO I=1 to 12 when your arrays are defined with only 5 elements?

What are you actually trying to do? 

Perhaps you really need two loops?  One over the variables and one over months?

[robulon]

Tom, the honest answer to your question is probably because I'm an idiot! I thought by saying do I = 8 - 12, it would only be populating 5 variables, hence my putting [5] in the array statement.

What I'm trying to do is depending on how many instalments there are for each specific observation, I want to populate that many of the date variables (so if there were 11 instalments, it would populate promise_1_date8 - promise_1_date11 and leave promise_1_date12 as a missing value).

To calculate the value to go into each date variable, I want to add the corresponding number of months to promise_1_date2.

It seemed straightforward in my head but is proving to be anything but (but I appreciate that is down to my SAS knowledge, or lack of!).

Thanks again,

Rob

[Tom]

Still not clear what you are trying to do. 

Is it always 8 to 11?   Does either number potentially change?

Do you want to define a full series of variables, say from 1 to 12, but then only populate those from 8 to 11 with non-missing values?

array pdate  promise_date1 - promise_date12 ;
do month=8 to 11 ;
  pdate[month]= ....
end;

Or do you only want to define variables for this restricted range?

Normally SAS will using indexes 1,2,3,4,.... for an array reference. 

array pdate promise_date8 - promise_date11 ;
do month=8 to 11 ;
  pdate[month-7]= ....
end;

But you can tell it to use a different range for the indexes.

array pdate [8:11] promise_date8 - promise_date11 ;
do month=8 to 11 ;
  pdate[month]= ....
end;

[robulon]

My starting data set already has promise_1_dates and amounts from 1 to 7, but there is another variable (instalments) which is the total number of payments due, which can go up to 12. I am trying to create additional variables 8 to 12 where necessary and populate them with the dates each of those variables is due, and the due date will always be the same day of the month as promise_1_date_2 (but not necessarily the same as promise_1_date_1 as there may be an immediate payment with subsequent payments scheduled in on a monthly basis).

What I was trying to do was to create those additional variables and depending on the number of instalments for each observation, populate it with the requisite number of dates leaving any that aren't required as missing.

The amount due will always be the same regardless of when the instalment is due, and will always be the same as promise_1_amt_2.

Thanks

[robulon]

And sorry, to clarify, the number in the date variable name does not necessarily correspond to calendar months (so promise_1_date8 is not necessarily in August), it is purely the number of months after the arrangement is set (which is stored in a separate variable).

[Tom · Accepted solution]

So just make the arrays include all of the variables. Then your indexes will work.  Note that there is no need to tell SAS how many variables there are in the array when you have listed their names.  It can count.

data test_2;
  set prom_201812;
  array pdate promise_1_date1 - promise_1_date12;
  array pamt  promise_1_amt1 - promise_1_amt12;
  if instalments > 7 then do i = 8 to 12;
    pdate[i] = intnx('month',pdate[2],i,'same');
    pamt[i] = pamt[2];
  end;
  format promise_1_date1 - promise_1_date12 ddmmyy10.;
  drop i;
run;

Personally I wouldn't use DDMMYY format. It will confuse half of your users.  If you use DATE or YYMMDD then nobody will confuse Dec 10th with Oct 12th.

[Astounding]

This solution appears to be most of the way there.  This statement looks incorrect:

if instalments > 7 then do i = 8 to 12;

I think you were looking for this instead:

if instalments > 7 then do i = 8 to instalments;

[robulon]

Thank you both so much, this is working perfectly and I have learnt a bit more about the use of arrays as well!

Tom, the existing dates in the dataset are already in the ddmmyy format, and being in the UK, this is generally how we do dates anyway but thanks for the pointer. Certainly if this was going to an international audience, it wold be something to bear in mind.

All the best,

Rob