turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- nested macro loop

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-18-2014 11:58 AM

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 num

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 torename

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)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-18-2014 12:50 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-18-2014 01:36 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-18-2014 01:37 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-18-2014 01:57 PM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-18-2014 04:21 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-18-2014 06:01 PM

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