topic Re: Looping through a Macro variable in SAS Programming

Looping through a Macro variable

Pumpp — Fri, 21 Feb 2020 12:38:31 GMT

Below is the code which works fine and gives results as expected.

%macro Variables(class1=);

proc tabulate data=work.policy_consolidated01 missing;
class year;
class quarter;
class type;
class Concat;

var No_of_policies;
var premium;
var Avg_Premium / weight = No_of_Policies;

table (year=' ')*(quarter=' ' all='Sub Total'),
sum=' '*(no_of_policies='NOP'*Format=comma16.0
premium='Prem'*Format=comma16.0)

mean=' '*(Avg_Premium='Avg Prem'*Format=comma16.0)
/printmiss nocellmerge box='year'; title &class1;
where year in ('2017','2016','2015', '2014', '2013') and Concat = &class1;
run;

%Mend;
%Variables(class1='AA');
%Variables(class1='AB');
%Variables(class1='AC');
%Variables(class1='BA');
%Variables(class1='BB');
%Variables(class1='CE');

etc....

There are 30 unique Concat observations. Instead of manually writing all the &class1 variables, I want a code such that it it loops through each of the 30 unique Concat observations and produces the same results.

Also is this looping possible if there are more than 1 class variables?

Re: Looping through a Macro variable

ed_sas_member — Fri, 21 Feb 2020 13:05:48 GMT

Hi @Pumpp

Could you please try this?

proc sql;
	create table Concat_list as select distinct Concat from work.policy_consolidated01;
quit;

data _null_;
	set Concat_list;
	rc = dosubl(cats('%Variables(class1="',Concat,'");'));
run;

proc sql;
	create table Concat_list as select distinct Concat from work.policy_consolidated01;
quit;

data _null_;
	set Concat_list;
	call execute (cats('%Variables(class1="',Concat,'");'));
run;

Re: Looping through a Macro variable

Quentin — Fri, 21 Feb 2020 13:07:08 GMT

Nice use of DoSubl!

Re: Looping through a Macro variable

Tom — Fri, 21 Feb 2020 13:45:14 GMT

Where does the list of values that you want to use in your macro calls come from? Do you have it in a dataset? Or just as a text string somewhere?

Do you really need to macro?

If the list is just from the same input dataset then why not just modify the TABLE statement to use CONCAT as the page dimension?

proc tabulate data=work.policy_consolidated01 missing;
  class year quarter type Concat;
  var No_of_policies premium;  
  var Avg_Premium / weight = No_of_Policies;
  table concat
      , (year=' ')*(quarter=' ' all='Sub Total')
      , sum=' '*(no_of_policies='NOP'*Format=comma16.0
        premium='Prem'*Format=comma16.0)
        mean=' '*(Avg_Premium='Avg Prem'*Format=comma16.0)
     /printmiss nocellmerge box='year'
  ; 
  where year in ('2017','2016','2015', '2014', '2013')
      and Concat in ('AA','AB')
  ;
run;

Or if the data is sorted then use it as a BY variable instead.

proc tabulate data=work.policy_consolidated01 missing;
  by concat;
  class year quarter type ;
  var No_of_policies premium;  
  var Avg_Premium / weight = No_of_Policies;
  table (year=' ')*(quarter=' ' all='Sub Total')
      , sum=' '*(no_of_policies='NOP'*Format=comma16.0
        premium='Prem'*Format=comma16.0)
        mean=' '*(Avg_Premium='Avg Prem'*Format=comma16.0)
    /printmiss nocellmerge box='year'
  ; 
  where year in ('2017','2016','2015', '2014', '2013')
      and Concat in ('AA','AB')
  ;
run;

Re: Looping through a Macro variable

Pumpp — Mon, 24 Feb 2020 04:49:28 GMT

I am getting an error message.

ERROR 180-322: Statement is not valid or it is used out of proper order.

Where do I put the %macro Variables(class1=); statement?

And also I want the Results(in tabular form) not output. Is it possible to use the dosubl syntax or call execute syntax in Proc tabulate?

Like i said, I have 30+ unique concat observations, and I want 30+ seperate results and then export those 30+ results in excel.

The method I used gave me the desired output, but i had to manually write all those 30+ class variables to get the output. I want a code like dosubl or call execute that can be used in porc tabulate to get the same results.

Re: Looping through a Macro variable

Pumpp — Mon, 24 Feb 2020 05:48:50 GMT

I do not want concat to come in TABLE statement. I want it in the same format as shown in the code.

And also I do not want the total of all concat's. I want the where statement of concat to be one those 30+ distinct observations. And i want 30+ different results displayed, rather than one single result.

I want to see the results based on individual concat values.

Re: Looping through a Macro variable

Tom — Mon, 24 Feb 2020 13:42:18 GMT

@Pumpp wrote:

I do not want concat to come in TABLE statement. I want it in the same format as shown in the code.

And also I do not want the total of all concat's. I want the where statement of concat to be one those 30+ distinct observations. And i want 30+ different results displayed, rather than one single result.

I want to see the results based on individual concat values.

Making the report BY the concat values will make a separate report for each value of CONCAT. You can either keep the automatic BY line or use the #BYVAL tag in your titles.

If you do want to generate one macro call per value of CONCAT you need to explain what is the source of the list of CONCAT values you want to use to drive the generation of the macro calls. Do you have that in a dataset?

Re: Looping through a Macro variable

Pumpp — Tue, 25 Feb 2020 05:36:58 GMT

Thank you very much.

The BY statement actually worked.