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;
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
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!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: