topic Re: %DO %UNTIL loop to a specific month in SAS Programming

%DO %UNTIL loop to a specific month

rajat051984 — Mon, 23 Mar 2015 13:58:02 GMT

Hi I want to execute the following loop till 201308, but macro variable are not getting resolved as desired. Kindly advice.

%let dater = '20MAR2015'D;

%macro rtlmn;
%let i = 0;
%let monum = 1;

%do %until (input(&monum,8.) = 201308);
%let i = %eval(&i+1);
%let datep = %sysfunc(intnx(month,&dater.,-1*&i,b)); /*loop to get previous months dates*/

data _null_;
set sashelp.class(obs=1);
prvmnt&i. = put(&datep.,monyy7.);
put prvmnt&i.;
call symput("prvmnt&i.",prvmnt&i.); /*prvmnt1, prvmnt2... will have values FEB2015, JAN2015... */

prev_mon&i. = year(&datep.)*100 + month(&datep.);
put prev_mon&i.;
call symput("prmn&i",prev_mon&i.); /*prmn1,prmn2,prmn3.. will have values 201502,201501, 201412 ..*/
run;

%let prmn&i. = &&&prmn&i.; /*to remove spaces from values*/
%let monum = &&&prmn&i.;
%end;

%mend rtlmn;
%rtlmn

ERROR: Required operator not found in expression: input(&monum,8.) = 201308

ERROR: The condition in the %DO %UNTIL loop, input(&monum,8.) = 201308, yielded an invalid or missing value, . The macro will stop executing.

ERROR: The macro RTLMN will stop executing.

Message was edited by: rajat panda

Re: %DO %UNTIL loop to a specific month

RW9 — Mon, 23 Mar 2015 14:16:28 GMT

Hi,

Can I ask, what are you trying to do here. Put some test data and required output. I can't see why this is in a macro, or using macro language. There are do loops in datastep?

Re: %DO %UNTIL loop to a specific month

Reeza — Mon, 23 Mar 2015 14:35:09 GMT

I also have no idea of what your macro is doing.

The following line requires the %sysfunc() for the inputc or inputn command as appropriate.

%do %until (input(&monum,8.) = 201308);

Then other errors pop up...good luck!

Re: %DO %UNTIL loop to a specific month

rajat051984 — Mon, 23 Mar 2015 14:38:34 GMT

Hi RW9,

Thanks fro your reply. I am trying to remove hard codings from below lines.

when sales_month = 201308 and transaction_type_id = 905 then retail else 0 end)as StarterKits_aug2013

This processing goes on till current month.

The modified code will look like -

when sales_month = &&prmn&i. and transaction_type_id = 905 then retail else 0 end)as StarterKits_&&prvmnt&i.

Kindly let me know if it can be achieved any other way.

Re: %DO %UNTIL loop to a specific month

RW9 — Mon, 23 Mar 2015 15:14:27 GMT

Well, there are several methods of getting there. The way I normally do it is with:

data have;

call execute('data want;');

do i=1 to intck('months','01Aug2013'd,today());

call execute(' mydate=intnx("month","01aug2013"d,'||put(i,2.)||',"same"); output;');

end;

call execute('format mydate date9.; run;');

run;

This generates more SAS code based on the loop. If you can provide some test data (in a datastep) and required output can clarify more.

Re: %DO %UNTIL loop to a specific month

rajat051984 — Mon, 23 Mar 2015 15:20:37 GMT

Thanks a lot Reeza. The mentioned error is gone. But I am facing another isse..

1 %macro rtlmn;

2 %let i = 0;

3 %let monum = 1;

4 %do %until (%sysfunc(inputn(&monum,8.) = 201408);

5 %let i = %eval(%bquote(&i)+1);

ERROR: Macro keyword LET appears as text.

ERROR: A dummy macro will be compiled.

6 %let datep = %nrstr(%sysfunc(intnx(month,&dater.,-1*&i,b)));

ERROR: Macro keyword LET appears as text.

8 data _null_;

9 set sashelp.class(obs=1);

10 prvmnt&i. = put(&datep.,monyy7.);

11 put prvmnt&i.;

12 call symput("prvmnt&i.",prvmnt&i.);

14 prev_mon&i. = year(&datep.)*100 + month(&datep.);

15 put prev_mon&i.;

16 call symput("prmn&i",prev_mon&i.);

17 run;

19 %let prmn&i. = &&&prmn&i.; /*to remove spaces from values*/

ERROR: Macro keyword LET appears as text.

20 %let monum = &&&prmn&i.;

ERROR: Macro keyword LET appears as text.

21 %end;

ERROR: Macro keyword END appears as text.

22 %mend rtlmn;

ERROR: Macro keyword MEND appears as text.

23 %rtlmn

I am trying to take help from thread- https://communities.sas.com/message/112741 but unable to resolve it. Can you please help ?

Re: %DO %UNTIL loop to a specific month

rajat051984 — Mon, 23 Mar 2015 16:12:41 GMT

Hi RW9,

Thanks for your reply. I am unfamiliar with Call Execute statement. I will look into it's documentation and get back to you. In the meanwhile, I am trying to solve this problem with macro variables. If you can kindly help me resolve the errors for the same, that would be kind of you.

Re: %DO %UNTIL loop to a specific month

Reeza — Mon, 23 Mar 2015 16:25:08 GMT

Well, if you want help you need to post a full reproducible example. What's the value of your macro variable dater?

EDIT:

Your original macro works fine for me once I set dater and fixed the input function. You need to post your actual code, or possibly restart SAS and see if the error is still present:

%let dater="01Jan2014"d;

%macro rtlmn;

%let i = 0;

%let monum = 1;

%do %until (%sysfunc(inputn(&monum,8.)) = 201308);

%let i = %eval(&i+1);

%let datep = %sysfunc(intnx(month,&dater.,-1*&i,b));

data _null_;

set sashelp.class(obs=1);

prvmnt&i. = put(&datep.,monyy7.);

put prvmnt&i.;

call symput("prvmnt&i.",prvmnt&i.);

prev_mon&i. = year(&datep.)*100 + month(&datep.);

put prev_mon&i.;

call symput("prmn&i",prev_mon&i.);

run;

%let prmn&i. = &&&prmn&i.; /*to remove spaces from values*/

%let monum = &&&prmn&i.;

%end;

%mend rtlmn;

%rtlmn

Re: %DO %UNTIL loop to a specific month

rajat051984 — Mon, 23 Mar 2015 16:49:58 GMT

Thanks Reeza. I solved the problem by using %sysfunc() and RESTARTS. Will this code be stable ? Would you like/advice me to use masking operators like %nrstr, %bquote in this code so that it maintains consistency in its functioning ?

Re: %DO %UNTIL loop to a specific month

Reeza — Mon, 23 Mar 2015 17:26:42 GMT

You don't need any masking as far as I can see.

It's stable as long as other parts are stable, as mentioned above this isn't a good way to do things but if it works, it works.