How to write a macro which can handle multiple values?

[SN1]

Hi all,

Can anyone help me with the following scenario?.

Consider a Sales Data. Suppose you have 3 Types of Orders and a variable Order_type having values either Type 1 Type 2 or Type 3 for Each Order Placed and you have another variable Quantity, having the total number of quantities ordered for each order. Write a Macro to display the frequency of quantity ordered for given order type in any particular year.

For example: If I mention value of year 2015 and type 3, It should display frequency of quantities placed in 2015 with type 3 with appropriate title. If I just mention year 2015, It should display frequency of all quantities ordered in 2015 with all types.

%macro freq(year=,type=);
proc sql;
select product_id,total_sales,order_type,year from(
select product_id,year(order_date) as year ,order_type ,sum(quantity) as total_sales from sales_data
group by 1,2,3)
where year=&year or order_type=&type;
quit;
%mend;
%freq(year=2007,type=1); /*to print the values of order_type=1 and year=2007*/

The 2nd parameter: “type” is optional. The code should be able to handle this case, which it is not handling currently.

Any help is appreciated. 

Thank you.

[novinosrin]

will this help?

%macro freq(year=,type=);
proc sql;
select product_id,total_sales,order_type,year from(
select product_id,year(order_date) as year ,order_type ,sum(quantity) as total_sales from sales_data
group by 1,2,3)
%if %length(&type)>0 %then %do;
where year=&year or order_type=&type;
%end;
%else %do;
where year=&year;
%end;
quit;
%mend;
%freq(year=2007,type=1); /*to print the values of order_type=1 and year=2007*/

[Astounding]

This is essentially the same suggestion, but simplified a little (where you see two semicolons in a row, be sure to include both):

%macro freq(year=,type=);
proc sql;
select product_id,total_sales,order_type,year from(
select product_id,year(order_date) as year ,order_type ,sum(quantity) as total_sales from sales_data
group by 1,2,3) where year=&year %if %length(&type)>0 %then or order_type=&type;
;
quit;
%mend;
%freq(year=2007,type=1); /*to print the values of order_type=1 and year=2007*/

[SN1 · Accepted solution]

i made a small change to the code and it worked.

i've put 'and' instead of 'or'

%macro freq(year=,type=);
proc sql;
select product_id,total_sales,order_type,year from(
select product_id,year(order_date) as year ,order_type ,sum(quantity) as total_sales from sales_data
group by 1,2,3)%if %length(&type)>0 %then %do;
where year=&year and order_type=&type;%end;%else %do;where year=&year;%end;
quit;
%mend;
%freq(year=2007);