SAS macro code to generate mean sales for a particular year

Question

I am trying to write a macro code that generates average sales for a particular year. Say today I want report for year=2008 and tomorrow for year=2009. The data has Order_date variable which is in format mmddyy10.

I am trying to achieve that by the following code:

%macro yearly(yr=%sysfunc(year("&sysdate"d )); 

proc means data=salesxls;
class &yr;
where year(order_date) == &yr;
title "yearly avg sales for &yr";
run;

%mend yearly;

This is not working. The reason could be the %sysfunc I used to extract the year. Any help would be appreciated. P.S A Macro newbie here

EDIT: The code for single year that works is as follows:

data new;

set work.salesxls;

yr=year(Order_date);     /** I extracted the year from Order_date)**/

run;

proc means data=new mean;

class yr;

var total_retail_price;

where yr=2008;

run;

In the code above I first created a data set which has extracted year. Then I used that data for proc means procedure.

To write a macro first start with working code. What does your proc means look like for a single year, with no macro logic that works. Then we can help you turn it into a macro. — Reeza
You may find these two links useful: This one shows how to use formats to summarize your data at differnet levels, ie year. gist.github.com/statgeek/… and this one shows how to turn a program into a macro: github.com/statgeek/SAS-Tutorials/blob/master/… — Reeza

Reeza Reeza · Accepted Answer · 2019-01-25T18:09:18

Here's how you can simplify this:

Move the YEAR calculation into the WHERE clause
Apply a format to the ORDER_DATE variable, YEAR4., which will display and aggregate it as a year.

Replace 2008 in code with &year.

%macro yearAvg(year=);
proc means data=salesxls mean;
where year(order_date)=&year.;
class order_date;
format order_date year4.;
var total_retail_price;
run;
%mend;

%yearAvg(year=2008);

SAS macro code to generate mean sales for a particular year

1 Answers