topic Re: ods tagsets.EXCELXP whit macro var in ODS and Base Reporting

ods tagsets.EXCELXP whit macro var

janne2 — Wed, 18 Nov 2009 13:07:52 GMT

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.

Re: ods tagsets.EXCELXP whit macro var

Flip — Wed, 18 Nov 2009 14:19:54 GMT

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

Re: ods tagsets.EXCELXP whit macro var

janne2 — Wed, 18 Nov 2009 14:24:15 GMT

How ? I don't understand what do you mean ?

Re: ods tagsets.EXCELXP whit macro var

sbb — Wed, 18 Nov 2009 14:45:01 GMT

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.

Re: ods tagsets.EXCELXP whit macro var

janne2 — Wed, 18 Nov 2009 15:13:14 GMT

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.

Re: ods tagsets.EXCELXP whit macro var

sbb — Wed, 18 Nov 2009 16:35:41 GMT

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.

Re: ods tagsets.EXCELXP whit macro var

sbb — Wed, 18 Nov 2009 16:38:24 GMT

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

Re: ods tagsets.EXCELXP whit macro var

Flip — Wed, 18 Nov 2009 18:54:17 GMT

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 ;