topic Re: nested macro loop in SAS Programming

nested macro loop

hatsumi — Wed, 18 Jun 2014 15:58:54 GMT

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;

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

Re: nested macro loop

Reeza — Wed, 18 Jun 2014 16:50:04 GMT

1. Use different counters.

2. How big are your datasets, if they're less than a billion rows all together probably worth using BY processing instead of macros. There's a good paper on this method called Don't Be Loopy

3. Do you have the SAS/ETS license?

Re: nested macro loop

hatsumi — Wed, 18 Jun 2014 17:36:16 GMT

Hi Reeza,

1. I didn't know that I could use different counter than "n"... and this was so simple fix, but it worked. Thanks!

2. My dataset is less than 1000 rows. I'm actually not quite sure how I can replace BY processing with macros. Do you have any links or reference materials?

3. Unfortunately no, I don't have an access to ETS license.

Re: nested macro loop

hatsumi — Wed, 18 Jun 2014 17:37:28 GMT

For your point 2, sorry I somehow missed your line about Don't be loopy. I'll read it. Thanks!!

Re: nested macro loop

Reeza — Wed, 18 Jun 2014 17:57:03 GMT

Based on what you've shown here's an example. It doesn't account for the lag though. If you don't have ETS you'll probably want to continue to use a macro to lag your data.

Hope this helps:

data sales;

informat date monyy7. store1 store2 store3 store4 8.;

format date monyy7.;

input date store1 store2 store3 store4;

cards;

Jan2004 44 85 33 20

Feb2004 46 90 50 22

Mar2004 48 91 40 18

;

run;

data Pop;

informat date monyy7. store1 store2 store3 store4 8.;

format date monyy7.;

input date store1 store2 store3 store4;

cards;

Jan2004 9955 12000 8000 7020

Feb2004 9977 12222 8055 7023

Mar2004 9366 12230 8040 7019

;

run;

data Price;

informat date monyy7. store1 store2 store3 store4 8.;

format date monyy7.;

input date store1 store2 store3 store4;

cards;

Jan2004 142 138 110 111

Feb2004 121 133 115 120

Mar2004 120 122 120 125

;

run;

proc transpose data=sales out=sales2 prefix=sales;

by date;

run;

proc transpose data=pop out=pop2 prefix=pop;

by date;

run;

proc transpose data=price out=price2 prefix=Price;

by date;

run;

data model;

merge sales2 pop2 price2;

by date _name_;

rename _name_ = store sales1=sales pop1=pop price1=price;

run;

proc sort data=model; by store date;

run;

proc reg data=model outest=reg_estimates;

by store;

model sales = price pop/noprint adjrsq rsquare;

run;

Re: nested macro loop

hatsumi — Wed, 18 Jun 2014 20:21:11 GMT

Hi Reeza,

Thanks for your code! It's interesting how you programmed it.

I wish I had ETS.

I was trying to find the reasons of the error and I found out that one of the variable in the real dataset had all missing values...

I learned that SAS gives this message if all values of a particular x variable is missing.

After removing this variable, the program runs smoothly with different counter as you suggested. Thank you for your help!

SAS: No valid observations are found Error - Simple Regression - Stack Overflow

Re: nested macro loop

Reeza — Wed, 18 Jun 2014 22:01:11 GMT

Everything I coded doesn't need ETS, ETS is useful for proc expand and creating the lags