Macro for historical data

[excelsas]

I have performance dataset in excel spreadsheet for companies in 50 states from Jan 2009.Each state have 2 or more companies and I have several performance metrices like profit amount, turnover rate , quantity used etc.
Date State Company Profit amount Turnover rate Quantity
July 2009 CA aaa 5,000$ 20% 35
Bbb 8,000$ 55% 42


I add new data for each state and company every month. I need to create charts for the past 18 months of data .
I tried to use month(today()) as current month (start date) and month(today())-12 as end date but it is not working.
Also how can I write a macro so that state I can import the data file every month and replace the old data set in sas and create state as macro variable so that I can type the required state and it will display the corresponding state data and then I can create the chart for the corresponding state?
I am new to the macros.
Any suggestion would be appreciated.

Thanks,
Blizzard

[barheat]

A couple of things to help you out. The first is using the INTNX function to define the date range. First off you want to create macro variables representing the past 12 months ending last month (since the current month is not complete):

%global prev_month &prev12_month;
data date_parms;
analysis_date=today();
prev_month=intnx('month',analysis_date,-1);
prev12_month=intnx('month',analysis_date,-12);
call symputx('prev_month',prev_month);
call symputx('prev12_month',prev12_month);
run;

You now have two macro variables representing the first day of the previous and 12 months ago.

To subset the data for a single state and the time period is simple;
Assumption is that the dta has been imported into SAS table PERFORMANCE.

%macro state_date(dsnout=,state=);
data &dsnout;
set performance;
where state="&state" and intnx('month',date,0) between &prev12_month and &prev_month;
run;

/* Insert chart code here */
%mend;

%state(dsnout=California, state=CA);
This will create a SAS table named California containing CA records for the past 12 months. If you insert chart code you can create a chart with the macro.

There are ways that you can build charts for all possible combinations of state and company by identifying unique combinations and then running them through a do loop. Hope this gets you in the right direction.

[excelsas]

In the macro, I m not getting any data in the dsnout data set, looks like set statement not working.
Any thoughts???

Thanks a lot for the help.

[barheat]

A mistake on my part. The macro name is state_date. I had tried to invoke macro state. The correct invocation in below.

%macro state_date(dsnout=,state=);
data &dsnout;
set performance;
where state="&state" and intnx('month',date,0) between &prev12_month and &prev_month;
run;

/* Insert chart code here */
%mend;

%state_date(dsnout=California, state=CA);

I assume that you are using an excel date for the date column in the spreadsheet and not a character string. If not, that could be a problem.