SAS - Use ODS Tagsets and macrovariables to print multiple datasetes to Excel

Question

I have multiple pre-existing datasets with similar names : EstOut_varname

I am trying to use ODS TAGSETS to output them all to Excel. I am also using a Macro function to loop over the similarly named datasets, like below. However, inside Proc Print, the macrovariable isn't dereferencing the way I thought it would.

Please advice - thanks for any help!

ods tagsets.excelxp style=sasweb
file="C:\Users\radhi\Desktop\estvars.xls";
%getBetas;
ods tagsets.excelxp close;


/*** Macro getBetas() to Loop over existing Datsets  that I am trying to output to Excel ***/
/*** The Datasets are all named similarly - EstVar_varname -EstVar_varn1,EstVar_varn2,EstVar_varn3  ***/

%macro getBetas();
%let varnamelist = varn1 varn2 varn3 ;
%put &varnamelist; 
%let end=%sysfunc(countw(&varnamelist));
%put &end;
%do i = 1 %to &end;
    /*** save the varname to macrovariable &out ***/
    %put out= %scan(&varnamelist., &i, " ");
    proc print data=EstVar_&out.;
    run;
%end;
%mend;

However, SAS does not dereference EstVar_&out. and does not recognise the &out :

proc print data=EstVar_&out.; run;
-
200
ERROR 200-322: The symbol is not recognized and will be ignored. 

---

NEVER MIND - I just needed to use %LET instead of %PUT inside the the %DO loop in the getBetas macro!

Answer 1

1. %macro ...; ... %mend; indeed defines a macro, but that only exists after the code is executed, zo define your macro before you *use it.
2. %put-statements are handy for debugging, but they don't assign a value to anything. You should use a %let-statement to give a macro variable a value.
3. Disclaimer: I never used ods tagsets, so I don't guarantee the below code is correct, but at least it handle macro's and macro variables correct.
4. I applied my personal preference for * ... *; comments over /* ... */ comment. Reserving the later for commenting out code allows you to comment out code with comments in it.
5. Why not integrate the ods statements in the macro?
6. Why not make the the dataset name prefix, the list of suffixes and the destination file parameters to %getBetas, so it becomes reusable?
7. Disclaimer: I did not test it, so I leave the debugging fun for you.
8. I stop here because you have a lot to learn already, but if it were for me, I would write a macro that prints all datasets with a name matching a SQL like condition, which would replace the first two macro parameters.

*** Macro getBetas writes similarly named datasets to excel.
    It accepts three parameters:
    - prefix : a common prefix for the dataset names
    - suffixes : a list of suffixes for the dataset names 
    - outFile : the full path of the destination excel file
***;
%macro getBetas(prefix, suffixes, outFile);
  %let suffix_count = %sysfunc(countw(&suffixes));
  %put NOTE: writing &suffix_count datasets 
       starting with &prefix and continuing with one of &suffixes, 
       separated by an underscore 
       to &outFile; 
  ods tagsets.excelxp style=sasweb file="&outFile";
  %do i = 1 %to &suffix_count;
    * save the varname to macrovariable &out *;
    %let out= &prefix._%scan(&suffixes., &i);
    %put NOTE: about to print &out to excel;
    proc print data=&out.;
    run;
  %end;
  ods tagsets.excelxp close;
%mend;

* Printing the similarly named datasets: EstVar_varn1, EstVar_varn2, EstVar_varn3 to excel *;
%getBetas(WORK.EstVar_, varn1 varn2 varn3, C:\Users\radhi\Desktop\estvars.xls);