Hi
I have 3 data files Sales, Population, Price for over 10 stores like below over a period of decades.
My aim is to forecast regional sales by running regressions on each store data and sum up each store's sales forecast to obtain the regional level at the end.
The first macro "stores" creates data files for each store. So this macro runs in a loop by a number of stores.
The second nested macro "regression" creates data file for 15 forecasting periods and run 15 regressions for each store.
Issue1:
I wrote my program below and it works fine if I just run each macro separately (not in a nested structure).
When I have a macro "regression" nested inside macro "stores", regression does not run and I get below error message.
ERROR: No valid observations are found.
NOTE: The data set WORK.STORE_REG1 has 0 observations and 3 variables.
but even in nested structure, all the store_data file for 15 forecasting periods were created successfully so I am not sure why SAS is saying that "No valid observations were found."
Issue2:
Another issue I encountered was that when &n reaches to 15 within macro "regression", the loop for macro "stores" also recognize &n as 15 and ends its loop.
So in my current program, the macro "stores" only runs for 1 iteration because after processing a macro "regression", &n eventually increments to 15 and takes this value as &n for the macro "stores" as well.
but I wanted SAS to recognize and treat &n for "stores" and "regression" macros differently.
I would really appreciate if anyone could let me know how to run program without error or do the same task in a much simpler code.
SALES
| Date | Store 1 | Store 2 | Store 3 | Store N |
|---|
| Jan 2004 | 44 | 85 | 33 | 20 |
| Feb 2004 | 46 | 90 | 50 | 22 |
| Mar 2004 | 48 | 91 | 40 | 18 |
POPULATION
| Date | Store 1 | Store 2 | Store 3 | Store N |
|---|
| Jan 2004 | 9955 | 12000 | 8000 | 7020 |
| Feb 2004 | 9977 | 12222 | 8055 | 7023 |
| Mar 2004 | 9366 | 12230 | 8040 | 7019 |
PRICE
| Date | Store 1 | Store 2 | Store 3 | Store N |
|---|
| Jan 2004 | 142 | 138 | 110 | 111 |
| Feb 2004 | 121 | 133 | 115 | 120 |
| Mar 2004 | 120 | 122 | 120 | 125 |
/*Create a dataset which contains all the store names*/
proc sql;
create table Storelist as
select varnum, name
from dictionary.columns
where memname ='SALES';
quit;
/*Create macro variable &Snum for the number of stores*/
proc sql noprint;
select nvar
into :Snum
from dictionary.tables
where libname='WORK' and memname='SALES';
quit;
%macro stores;
%do n=1 %to &Snum;
/*Create macro variable &Storename for a loop*/
proc sql noprint;
select distinct name
into :Storename
from Storelist
where varnum=&n;
quit;
/*Retrieve necessary variables to run regressions and create a data file for each store*/
Data Store_&Storename;
set SALES(keep=&Storename rename=(&Storename=SALES));
set POPULATION(keep=&Storename rename=(&Storename= POPULATION));
set PRICE(keep=&Storename rename=(&Storename= PRICE));
run;
/*Obtain sales forecasts over next 15-month with regressions*/
/*If forecasting beyond 12-month, drop lagged variable in the regression*/
%macro regression;
%do n=1 %to 15;
data STORE_DATA&n;
set Store;
SALES_L=lag&n(SALES);
%if &n<12 %then %do;
proc reg data= STORE_DATA&n outest=STORE_Reg&n TABLEOUT;
Horizon&n: model SALES=SALES_L PRICE POPULATION/NOPRINT ADJRSQ RSQUARE;
%end;
%else %do;
Horizon&n: model SALES=PRICE POPULATION/NOPRINT ADJRSQ RSQUARE;
RSQUARE;
%end;
%end;
%mend;
%regression
…(Here, I have codes to calculate predictions from regressions for each store because I have specific XY values I want to use to obtain regression forecasts.
This part of codes also iterates by the number of stores)
%end;
%mend;
%stores
…(Here, I plan to write codes to aggregate sales predictions from each store to obtain the regional total sales)
