Use ODS Tagsets and macrovariables to print multiple datasetes to Excel

[nstdt]

I have multiple 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 names 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 to Loop over existing Datsets  that I am trying to output to Excel ***/
/*** The Datasetes are all named EstVar_varname ***/

%macro getBetas();
%let varnamelist = varn1 varn2 varn3 ;
%put &varnamelist; 
%let end=%sysfunc(countw(&varnamelist));
%put &end;
%do i = 1 %to &end;
	%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.

[PaigeMiller]

Whenever you have an ERROR in the log, you need to show us the entire log for this macro. Do not show us ERROR messages detach from the code. Since it is a macro, turn on the macro debugging option by running this line of code

options mprint;

and then run your macro again and show us the entire log for the macro. Please copy the log as text and paste it into the window that appears when you click on the </> icon.

In addition, if you use ODS Excel, you will not need a macro. You can use BY group processing to route the results from each BY group to a new tab in Excel, said tab named by the value of the BY group. So from my point of view, macro is the wrong solution and is a solution that is unnecessarily complex.

[nstdt]

Here is the Log:

2505  dm "log; clear; ";
2506  dm 'odsresults; clear';
2507  options mprint;

2508  /* */
2509  %macro getBetas();
2510  %let varnamelist = SLSS3 PANASn3 PANASp3 /*PANASpP3 PANASnP3 CES3 CHSa3 CHSp3 CHStot3 CASSSC3
2510!  CASSSP3 CASSST3*/ ;
2511  %put &varnamelist;
2512  %let end=%sysfunc(countw(&varnamelist));
2513  %put &end;
2514  %do i = 1 %to &end;
2515      %put out= %scan(&varnamelist., &i, " ");
2516      proc print data=EstCovWithBL_&out.;
2517      run;
2518  %end;
2519  %mend;
2520
2521  ods tagsets.excelxp style=sasweb
2522  file="C:\Users\radhi\OneDrive - University of Delaware - o365\Desktop\Dr Suldo
2522! IES\estbetas.xls";
NOTE: Writing TAGSETS.EXCELXP Body file: C:\Users\radhi\OneDrive - University of Delaware -
      o365\Desktop\Dr Suldo IES\estbetas.xls
NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.131, 04/23/2015). Add
options(doc='help') to the ods statement for more information.
2523  %getBetas;
SLSS3 PANASn3 PANASp3
3
out= SLSS3
NOTE: Writing HTML Body file: sashtml4.htm
WARNING: Apparent symbolic reference OUT not resolved.
NOTE 137-205: Line generated by the invoked macro "GETBETAS".
1      proc print data=EstCovWithBL_&out.;     run;
                                    -
                                    22
ERROR 22-322: Syntax error, expecting one of the following: ;, (, BLANKLINE, CONTENTS, DATA,
              DOUBLE, GRANDTOTAL_LABEL, GRANDTOT_LABEL, GRAND_LABEL, GTOTAL_LABEL, GTOT_LABEL,
              HEADING, LABEL, N, NOOBS, NOSUMLABEL, OBS, ROUND, ROWS, SPLIT, STYLE, SUMLABEL,
              UNIFORM, WIDTH.

NOTE: Line generated by the invoked macro "GETBETAS".
1      proc print data=EstCovWithBL_&out.;     run;
                                    -
                                    200
ERROR 200-322: The symbol is not recognized and will be ignored.

WARNING: Apparent symbolic reference OUT not resolved.
ERROR: File WORK.ESTCOVWITHBL_.DATA does not exist.
MPRINT(GETBETAS):   proc print data=EstCovWithBL_&out. run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.43 seconds
      cpu time            0.15 seconds

out= PANASn3


WARNING: Apparent symbolic reference OUT not resolved.
NOTE 137-205: Line generated by the invoked macro "GETBETAS".
3          proc print data=EstCovWithBL_&out.;     run;
                                        -
                                        22
ERROR 22-322: Syntax error, expecting one of the following: ;, (, BLANKLINE, CONTENTS, DATA,
              DOUBLE, GRANDTOTAL_LABEL, GRANDTOT_LABEL, GRAND_LABEL, GTOTAL_LABEL, GTOT_LABEL,
              HEADING, LABEL, N, NOOBS, NOSUMLABEL, OBS, ROUND, ROWS, SPLIT, STYLE, SUMLABEL,
              UNIFORM, WIDTH.

NOTE: Line generated by the invoked macro "GETBETAS".
3          proc print data=EstCovWithBL_&out.;     run;
                                        -
                                        200
ERROR 200-322: The symbol is not recognized and will be ignored.

WARNING: Apparent symbolic reference OUT not resolved.
ERROR: File WORK.ESTCOVWITHBL_.DATA does not exist.
MPRINT(GETBETAS):   proc print data=EstCovWithBL_&out. run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

out= PANASp3


WARNING: Apparent symbolic reference OUT not resolved.
NOTE 137-205: Line generated by the invoked macro "GETBETAS".
5          proc print data=EstCovWithBL_&out.;     run;
                                        -
                                        22
ERROR 22-322: Syntax error, expecting one of the following: ;, (, BLANKLINE, CONTENTS, DATA,
              DOUBLE, GRANDTOTAL_LABEL, GRANDTOT_LABEL, GRAND_LABEL, GTOTAL_LABEL, GTOT_LABEL,
              HEADING, LABEL, N, NOOBS, NOSUMLABEL, OBS, ROUND, ROWS, SPLIT, STYLE, SUMLABEL,
              UNIFORM, WIDTH.

NOTE: Line generated by the invoked macro "GETBETAS".
5          proc print data=EstCovWithBL_&out.;     run;
                                        -
                                        200
ERROR 200-322: The symbol is not recognized and will be ignored.

WARNING: Apparent symbolic reference OUT not resolved.
ERROR: File WORK.ESTCOVWITHBL_.DATA does not exist.
MPRINT(GETBETAS):   proc print data=EstCovWithBL_&out. run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

2524  ods tagsets.excelxp close;

[nstdt]

Thanks! Do you have a small example to share? I would like to avoid using a Macro if possible!

[PaigeMiller]

---

@nstdt wrote:
Thanks! Do you have a small example to share? I would like to avoid using a Macro if possible!

---

Example using  SASHELP.CARS

ods excel file="excel_file_name.xlsx" options(sheet_interval='BYGROUP' sheet_name='#byval1');
proc print data=sashelp.cars;
    by make;
run;
ods excel close;

[nstdt]

Thanks for the example. Quick followup - what if I have multiple datasets that need to output to different Excel sheets? So, my data is in different datasets - dat1,dat2,...,datN and each of these need to output to their own separate tab in one single Excel file. I can't think of a single common BY variable (except perhaps the Row Number). How could I modify your example code - any suggestions would help!

[PaigeMiller]

You combine all of these data sets into one large data set, the BY variable becoming the data set name.

Example:

data big;
    set dataset1 dataset2 dataset3 indsname=indsname;
    datasetname=indsname;
/* More data step commands if needed */
run;

[Tom]

---

@nstdt wrote:

Thanks for the example. Quick followup - what if I have multiple datasets that need to output to different Excel sheets? So, my data is in different datasets - dat1,dat2,...,datN and each of these need to output to their own separate tab in one single Excel file. I can't think of a single common BY variable (except perhaps the Row Number). How could I modify your example code - any suggestions would help!

---

Assuming that this other statement you made is also true:

What I meant is that there are several different datasets to export. How can I manage this with a single BY group ? The column names are the same but the name of each dataset is different.

Then it might help to make a common dataset and then print that.  You can use the INDSNAME= option of the SET statement to generate a variable with the name of the dataset.  You could then define a sheetname from the dataset name.

data for_print;
  set dat1 dat2 datN indsname=dsname;
  sheetname = scan(dsname,-1,'.');
run;

And then use the sheetname variable as your BY variable.

[nstdt]

Thanks, this comes closest to my requirements.

I originally was working with datasets with common column names, but later realised I may have to generalize this to when there are no common column names.

I can, however, create a common variable - a  row number column. Or, it would seem, from your reply and @PaigeMiller , I can make the BY variable  in PROC Excel just be the datasetname (where datasetname is the data set that combines all the original datasets).

Thanks again.

[nstdt]

What I meant is that there are several different datasets to export. How can I manage this with a single BY group ? The column names are the same but the name of each dataset is different.

[SASKiwi · Accepted solution]

This works without error and will get you closer to what you want:

%macro getBetas();
%let varnamelist = varn1 varn2 varn3 ;
%put &varnamelist; 
%let end=%sysfunc(countw(&varnamelist));
%put &end;
%do i = 1 %to &end;
	%let out = %scan(&varnamelist., &i);
	%put out = &out.;
%end;
%mend;

%getBetas;

[nstdt]

Yes, thanks! I was using "PUT" instead of "LET"!

[Patrick]

If I understand correctly what you're trying to do then consider the following alternative coding approach.

%let xlsx_workbook=c:\temp\my_workbook.xlsx;

/* delete pre-existing Excel workbook if it exists */
data _null_;
  fname='__fref';
  rc=filename(fname, "&xlsx_workbook");
  if rc = 0 and fexist(fname) then
    rc=fdelete(fname);
  rc=filename(fname);
run;

/* create Excel workbook and copy desired tables into it */
libname wantxlsx xlsx "&xlsx_workbook";
proc datasets lib=wantxlsx nolist;
  copy
    in=sashelp
    out=wantxlsx
    ;
  select cars class;
  run;
quit;
libname wantxlsx clear;