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.
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);
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*/
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*/
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);
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.