topic Re: How can access a macro do loop (or do loop only) for a list of items ? in SAS Programming

How can access a macro do loop (or do loop only) for a list of items ?

wtien196838 — Fri, 30 Sep 2016 15:44:39 GMT

Hi SAS Community,

I have a list of item ids such as:

%let item_id = 1234 2456 3567 4567 5674 8765 ;

I want to put in a do loop like:

%DO i = &item_id ;

/* call one macro and input item id */

%macro_call(&i)

%END ;

The %macro_call is working with individual item id such as

%macro_call(1234)

Thank you for sharing your knowledge.

WT1968

Re: How can access a macro do loop (or do loop only) for a list of items ?

RW9 — Fri, 30 Sep 2016 15:49:06 GMT

Simple answer, as with all these things, use Base SAS:

data _null_;
  do i=1 to countw("&ITEM_ID."," ");
    call execute(cats('%macro (',scan("&ITEM_ID.",i," "),");"));
  end;
run;

In fact, as with any question stating "Macro" use Base SAS, macro is only there to generate code, Base SAS has all the functionality, and all the datatypes.

Re: How can access a macro do loop (or do loop only) for a list of items ?

ballardw — Fri, 30 Sep 2016 15:53:26 GMT

One way IF your list never contains commas or special characters like % & and such.

%macro control (list);
   %local list;
   %do i=1 %to %sysfunc(countw(&list));
      %let key = %scan(&list,&i);

       %macro_call(&key);
   %end;
%mend;

%control(1234 2456 3567 4567 5674 8765);

Another would be to have the values as one record per value in a data set and use a data step with Call Execute.

Re: How can access a macro do loop (or do loop only) for a list of items ?

Reeza — Fri, 30 Sep 2016 18:04:04 GMT

See the examples in the Appendix

https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Appendix/ta-p/291716

Re: How can access a macro do loop (or do loop only) for a list of items ?

wtien196838 — Sat, 01 Oct 2016 01:07:58 GMT

Thanh you for Ballardw and Reeza quick response.

Both solutions are resolved my working problem.

Now I have an advanced question based on previous question.

Now I have:

%let item_id = 1234 2456 3567 4567 5674 ;

%let start_date = '03-SEP-16' '10-SEP-16' '17-SEP-16' '24-SEP-16' '01-OCT16' ;

%let end_date = '09-SEP-16' '16-SEP-16' '23-SEP-16' '30-SEP-16' '07-OCT-16';

I want to put in a do loop such that

DO i = &item_id.

%macro_call(item_id,start_date,end_date)

End;

Macro call ask for each item associate date range. Example: item_id=1234 must link to "03-SEP-16' and '09-SEP-16' etc..

It is very helpful to get a solution for this problem.

Thanks in advance.

WT196838

Re: How can access a macro do loop (or do loop only) for a list of items ?

Reeza — Sat, 01 Oct 2016 01:39:01 GMT

I would suggest instead putting the values in a data set and then using a call execute to call your macro.

Otherwise you can use scan() or %scan() to isolate each item in turn and pass it to the macro.

Re: How can access a macro do loop (or do loop only) for a list of items ?

ballardw — Mon, 03 Oct 2016 16:15:42 GMT

As a general comment: placing quote marks into macro variables seldom yields clean code. I would say that it usually is easier to leave the macro parameter as plain text and then if, and only if, you are referencing the value in code that requires the value to place it DOUBLE quotes for use.

If your end and start date parameters exactly match the number of ITEMS then it is a trivial programming exercise to extend the provided code:

%macro control (list=, start_date=, end_date=);
   %local list;
   %do i=1 %to %sysfunc(countw(&list));
      %let key = %scan(&list,&i);
      %let key2= %scan(&start_date,&i); /* you already had the pattern for this statement*/
      %let key3= %scan(&start_date,&i);

       %macro_call(&key,&key2,&key3);
   %end;
%mend;

%control(list=1234 2456 3567 4567 5674 8765,
         start_date= 03-SEP-16  10-SEP-16 17-SEP-16 24-SEP-16 01-OCT16,
         end_date  = 09-SEP-16 16-SEP-16 23-SEP-16 30-SEP-16 07-OCT-16 );

I strongly recommend using keyword parameters if there are many parameters involved and especially if there are longish lists of items.

Re: How can access a macro do loop (or do loop only) for a list of items ?

wtien196838 — Mon, 03 Oct 2016 17:44:51 GMT

Thank you for Reeza and ballardw again for your correct solution.

I also contribute my little discovery from this code. My macro goes through an oracle sql which some conditions such as

proc sql ;

select * from table;

code 1: where var BETWEEN &start_date. and &end_date. (macro resolved, sql error)

code 2: where var BETWEEN "&start_date." and "&end_date." (macro resolved, sql error)

code 3: where var between '&start_date.' and '&end_date.' (macro unresolved, sql error)

code 4: where var between %nrbquote('&start_date.') and %nrbquote('&end_date.') (macro resolved, sql correct)

I have searching for long time how to resolve the macro within quote '&macro_varibale'. With macro function %nrbquote , it worked.

Thanks for all contributions on this topic. Good job SAS community.

WT196838