How to write a macro which can handle multiple values?

Accepted Solution Solved
Reply
Occasional Contributor SN1
Occasional Contributor
Posts: 5
Accepted Solution

How to write a macro which can handle multiple values?

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.


Accepted Solutions
Solution
4 weeks ago
Occasional Contributor SN1
Occasional Contributor
Posts: 5

Re: How to write a macro which can handle multiple values?

Posted in reply to novinosrin

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);

 

View solution in original post


All Replies
Super User
Posts: 2,075

Re: How to write a macro which can handle multiple values?

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*/

 

Super User
Posts: 6,939

Re: How to write a macro which can handle multiple values?

[ Edited ]
Posted in reply to novinosrin

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*/

 

Solution
4 weeks ago
Occasional Contributor SN1
Occasional Contributor
Posts: 5

Re: How to write a macro which can handle multiple values?

Posted in reply to novinosrin

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);

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 73 views
  • 0 likes
  • 3 in conversation