BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
slottemc
Obsidian | Level 7

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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
--
Paige Miller

View solution in original post

3 REPLIES 3
PaigeMiller
Diamond | Level 26

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
--
Paige Miller
Reeza
Super User

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;

 


 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 456 views
  • 0 likes
  • 3 in conversation