DATA Step, Macro, Functions and more

nested macro loop

Reply
Contributor
Posts: 30

nested macro loop

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

DateStore 1Store 2Store 3Store N
Jan 200444853320
Feb 200446905022
Mar 200448914018

POPULATION

DateStore 1Store 2Store 3Store N
Jan 200499551200080007020
Feb 200499771222280557023
Mar 200493661223080407019

PRICE

DateStore 1Store 2Store 3Store N
Jan 2004142138110111
Feb 2004121133115120
Mar 2004120122120125

/*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 Smiley Frustratednum

     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 Smiley Frustratedtorename

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)

Super User
Posts: 17,829

Re: nested macro loop

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?

Contributor
Posts: 30

Re: nested macro loop

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.

Contributor
Posts: 30

Re: nested macro loop

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

Super User
Posts: 17,829

Re: nested macro loop

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;

Contributor
Posts: 30

Re: nested macro loop

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

Super User
Posts: 17,829

Re: nested macro loop

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

Ask a Question
Discussion stats
  • 6 replies
  • 980 views
  • 7 likes
  • 2 in conversation