tagsets.excelxp, proc print, and a macro

[hseid]

Hi --

Note:  I solved this by moving the ODS sheet name statement.  See bottom of this post.  THanks to all.

I'm using a macro to run a large number of proc prints variables, one at a time.  I start by opening an excel file, and then I call the macro which sets the sheet name to the variable name  and runs proc print. (proc print; var xxx; run;)

For some reason, even though I assign the sheet name before the proc print, I get the name of the next variable in the list as the name of the sheet -- for example, if I have  v1, v2, v3, I would like to name the first sheet v1, and have the proc print of v1 on that sheet, the next sheet v2 with proc print of v2, etc.  However, I'm getting v2 on a sheet named v3.

If I use the same code but run a proc tabulate, the assignment of the sheet name works fine.  It seems to have something to do with proc print, as if for some reason the name assignment is not picked up until after the proc print executes, even though it's before the proc print in the code.

Some code follows.  Qstring is a list of variable, qct is the number of variables, and qtype is whether the variable is a text variable (in which case print it) or a numeric variable (in which case run proc tabulate):

ods  tagsets.excelxp

file= "pathandname.xlm" style =xlmeadow  options (embedded_footnotes= 'YES' embed_titles_once='YES' );

  
%macro runtab;
   %do i = 1 %to &qct;
       ods tagsets.excelxp options (frozen_headers='yes' sheet_name = "%scan(&qstring,&i)" );
     %if %trim(%scan(&qtype,&i)) = T %then %do;
              data _null_; run;  *** I added this to see if having something happen before the proc print would help; it did not.
              proc print data = prep (where=(%scan(&qstring,&i) ~= ""));
     var customid orgstatus %scan(&qstring,&i);
     format orgstatus orgstat.;
     title2 "Opened-ended questions";
       %end;
       %else %do;
          proc freq data = prep noprint;
          tables OrgStatus * %scan(&qstring,&i)/out=xxx;
          run;  

          proc tabulate data = xxx format=10.0;
          var count;
          class OrgStatus %scan(&qstring,&i);
         tables %scan(&qstring,&i), OrgStatus * count=""*(sum="N"  colpctsum="%"*f=10.1) count="Overall"*(sum="N" colpctsum= "%") /box = "%scan(&qstring,&i)";
         title2 "Frequencies with missing dropped";
         label orgstatus ="Organization status";
         format orgstatus orgstat.;
         run;
      %end;
   %end;
%mend;

%runtab;

ods tagsets.excelxp close;

Thanks for any help you can give --

Heather

Here's the revised code that works with the changes highlighted in red:

%macro runtab;
   %do i = 1 %to &qct;
     %if %trim(%scan(&qtype,&i)) = N %then %do;
          proc freq data = prep noprint;
          tables OrgStatus * %scan(&qstring,&i)/out=xxx;
          run;

          ods tagsets.excelxp options (frozen_headers='yes' sheet_name = "%scan(&qstring,&i)" );
          proc tabulate data = xxx format=10.0;
          var count;
          class OrgStatus %scan(&qstring,&i);
          tables %scan(&qstring,&i), OrgStatus * count=""*(sum="N"  colpctsum="%"*f=10.1) count="Overall"*(sum="N" colpctsum= "%") /box = "%scan(&qstring,&i)";
          title2 "Frequencies with missing dropped";
          label orgstatus ="Organization status";
          format orgstatus orgstat.;
          run;
       %end;
       %else %do;
              data _null_; run;
             ods tagsets.excelxp options (frozen_headers='yes' sheet_name = "%scan(&qstring,&i)" );
              proc print data = prep (where=(%scan(&qstring,&i) ~= ""));
              var customid orgstatus %scan(&qstring,&i);
              format orgstatus orgstat.;
              title2 "Opened-ended questions";
              run;

      %end;
   %end;
%mend;

[ballardw]

One thing I would try in debugging is to create a new temporary local variable to hold the name of the variable instead of having so many calls to %scan(&qstring,&i)

Something like:

%local varname;

%do i = 1 to &qct;

     %let varname = %scan(&qstring,&i);

and replace the calls to %scan with &varname.

If nothing else part of the code gets easier to read