BookmarkSubscribeRSS Feed
janne2
Calcite | Level 5
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.
7 REPLIES 7
Flip
Fluorite | Level 6
First of all move the ODS open command inside you macro. You are opening a file once and closing it 95 times.
janne2
Calcite | Level 5
How ? I don't understand what do you mean ?
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
janne2
Calcite | Level 5
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.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Flip
Fluorite | Level 6
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 ;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 785 views
  • 0 likes
  • 3 in conversation