Run a macro with a changing input dataset each time without having to specify the input dataset

[slottemc]

Hi, 

So I want to run the below macro using &name as the input dataset, but I have over a hundred datasets I need to run this through. I originally wanted to do the macro as a by group but it does not look like it has that functionality and instead has to be run one at a time. What is the best way to change the input dataset names?

%forecast (data=unixwork.&name, var=rate, id=date, interval=month,
           project=projtest_fcb, entry=forecast, out=unixwork.forecast_fcb, 
           climit=95, horizon=4, keep=1);

The values of &name would be...

1)BCS_CHMO

2)BCS_CPPO

3)BCS_XHMO

4)COL_CHMO

5)COL_CPPO

6)CISDTP_XHMO

7)PPC1_XPPO

etc. like I said over 100 of these values

I had all of these in a KEY column in my original table, but then I split it all out into their own datasets, but now I think I didn't need to do that, but probably could have used some type of call symput or %Syscall set to change the value of &name and run the macro and keep doing until each &name is run?

data input;
input KEY product measure $ date rate;
datalines ;
BCS_CHMO     CHMO BCS    01JAN2016 .5432
BCS_CPPO     CPPO BCS    01JAN2016 .4567
BCS_XHMO     XHMO BCS    01JAN2016 .4321
COL_CHMO     CHMO COL    01JAN2016 .5678
COL_CPPO     CPPO COL    01JAN2016 .4567
CISDTP_XHMO  XHMO CISDTP 01JAN2016 .2345
PPC1_XPPO    XPPO PPC1   01JAN2016 .3210
;
data _null_;
 if _n_=1 then do;
   if 0 then set input;
   declare hash h(dataset:'input(obs=0)',multidata:'y');
   h.definekey(all:'y');
   h.definedata(all:'y');
   h.definedone();
 end;
do until(last.key);
 set input;
 by key;
 h.add();
end;
h.output(dataset:key);
h.clear();
run;

[PaigeMiller · Accepted solution]

You can extract all the data set names in a library by using PROC SQL, and then either CALL EXECUTE or a macro will do the looping. 

Example:

%macro do_all;
proc sql noprint;
    select memname into :dsnames separated by ' ' from dictionary.tables
        where upcase(libname)='UNIXWORK';
quit;

%do i=1 %to %sysfunc(countw(&dsnames));
    %let thisname=%scan(&dsnames,&i,%str( ));
    %forecast(data=unixwork.&thisname, ...)
%end;
%mend
%do_all

[slottemc]

Thank you. Worked perfect!

[Reeza]

CALL EXECUTE to run them all. 

The documentation has an example or I have an example at the end of this tutorial

https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

FYI - make sure to uniquely name each output file.

---

@slottemc wrote:

Hi, 

So I want to run the below macro using &name as the input dataset, but I have over a hundred datasets I need to run this through. I originally wanted to do the macro as a by group but it does not look like it has that functionality and instead has to be run one at a time. What is the best way to change the input dataset names?

%forecast (data=unixwork.&name, var=rate, id=date, interval=month,
           project=projtest_fcb, entry=forecast, out=unixwork.forecast_fcb, 
           climit=95, horizon=4, keep=1);

The values of &name would be...

1)BCS_CHMO

2)BCS_CPPO

3)BCS_XHMO

4)COL_CHMO

5)COL_CPPO

6)CISDTP_XHMO

7)PPC1_XPPO

etc. like I said over 100 of these values

I had all of these in a KEY column in my original table, but then I split it all out into their own datasets, but now I think I didn't need to do that, but probably could have used some type of call symput or %Syscall set to change the value of &name and run the macro and keep doing until each &name is run?

data input;
input KEY product measure $ date rate;
datalines ;
BCS_CHMO     CHMO BCS    01JAN2016 .5432
BCS_CPPO     CPPO BCS    01JAN2016 .4567
BCS_XHMO     XHMO BCS    01JAN2016 .4321
COL_CHMO     CHMO COL    01JAN2016 .5678
COL_CPPO     CPPO COL    01JAN2016 .4567
CISDTP_XHMO  XHMO CISDTP 01JAN2016 .2345
PPC1_XPPO    XPPO PPC1   01JAN2016 .3210
;
data _null_;
 if _n_=1 then do;
   if 0 then set input;
   declare hash h(dataset:'input(obs=0)',multidata:'y');
   h.definekey(all:'y');
   h.definedata(all:'y');
   h.definedone();
 end;
do until(last.key);
 set input;
 by key;
 h.add();
end;
h.output(dataset:key);
h.clear();
run;

---