The SAS Output Delivery System and reporting techniques

ods tagsets.EXCELXP whit macro var

Reply
New Contributor
Posts: 3

ods tagsets.EXCELXP whit macro var

Hi there,

I would like print to Excel sheets 380 tables "sas" by using 4 "proc FREQ"

This is my program :

ods tagsets.EXCELXP file="D:\S\&school.xls" ;

%macro test ;
%do cpt = 1 %to 95 ;
%if &cpt =1 %then %let school =0372604S ;
%if &cpt =2 %then %let school =0370049P ;
%if &cpt =3 %then %let school =0370002N ;

proc freq data= a.table (where=(school="&school"));
table school*var1*var2*var3/ missprint nocol nopercent nofreq norow noprint out= sasuser.tbx1&school ;
format school $school. ;
run;
ods tagsets.Excelxp close;
%end ;
%mend ;
%test ;

Could you please find out any anomalies in this program, and if you could
propose any alternatives that could fix this program.

Thank for your assistance in advance.
Super Contributor
Posts: 359

Re: ods tagsets.EXCELXP whit macro var

First of all move the ODS open command inside you macro. You are opening a file once and closing it 95 times.
New Contributor
Posts: 3

Re: ods tagsets.EXCELXP whit macro var

How ? I don't understand what do you mean ?
Super Contributor
Super Contributor
Posts: 3,174

Re: ods tagsets.EXCELXP whit macro var

Add the following SAS command to help you self-diagnose the problem -- you will see how SAS is resolving your macro variable. More specifically, you declared the macro variable at a point where it either has not value or the value is static, and then you intend to iterate with the %DO / %END. Review the SAS log after adding:

OPTIONS SOURCE SOURCE2 MACROGEN SYMBOLGEN MLOGIC;

You will see the processing in detail -- hopefully that will help.

Scott Barry
SBBWorks, Inc.
New Contributor
Posts: 3

Re: ods tagsets.EXCELXP whit macro var

You're reason, i've mooved the ods under the macro, but the proc freq das not work because of the "&" (table var1*var2*var3/nopercent......out = sasuser.&scholl);

log: Apparent symbolic reference school not resolved.

Please help me, i'am new sas user.

Thank's a lot.
Super Contributor
Super Contributor
Posts: 3,174

Re: ods tagsets.EXCELXP whit macro var

Your code does no assign a variable value to SCHOOL in all cases, so you are getting an unresolved macro variable error. So, your %LET logic needs to be complete for all possible CPT values -- possibly just adding a %ELSE %LET assignment statement -- I don't know your application's needs though, so you must decide, or maybe a null value?.

Scott Barry
SBBWorks, Inc.
Super Contributor
Super Contributor
Posts: 3,174

Re: ods tagsets.EXCELXP whit macro var

Being a new SAS user, maybe some basics review is in order. It's unclear how you can expect to support your own code, if you cannot understand how it is executing. That was one reason for suggesting you expand the amount of SAS-generated diagnostics information to your log.

Scott Barry
SBBWorks, Inc.

SAS Macro Language: Reference, Introduction to the Macro Facility
http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/a002293969.htm
Super Contributor
Posts: 359

Re: ods tagsets.EXCELXP whit macro var

if the line ods tagsets.EXCELXP file="D:\S\&school.xls" ; is not inside the macro then &school has not been assigned. Secondly the macro var school resolving within a string needs a terminator. SAS assumes this is what the . is for so you will get no . between the name and xls. You need to write this as
ods tagsets.EXCELXP file="D:\S\&school..xls" ; with .. after the &school.

As Scott pointed out you seem to be having trouble with very basic concepts of macros, and need to get a handle on these. If it will help you, I might write this code something like the following.

data _null_;
input school $;
call symput(compress("school" || put(_n_, best.)), school);
cards;
0372604S
0370049P
0370002N
;;;
run;

%macro test ;


%do cpt = 1 %to 3 ;
%put &&school&cpt ;

ods tagsets.EXCELXP file="D:\S\&&school&cpt..xls" ;
proc freq data= a.table (where=(school="&&school&cpt"));
table school*var1*var2*var3/ missprint nocol nopercent nofreq norow noprint out= sasuser.tbx1&&school&cpt ;
format school $school. ;
run;
ods tagsets.Excelxp close;
%end ;
%mend ;
%test ;
Ask a Question
Discussion stats
  • 7 replies
  • 180 views
  • 0 likes
  • 3 in conversation