Community - I am having difficulty troubleshooting an issue. I have two macros below. The second macro callbreak(), looks at a data set, and at each first.ID it calls the break macro. The break() macro creates a new data set selecting obs from the original data for that ID. So simply, it is creating a data set for each new unique ID. Break() then saves a macro description from a value in the data set so it can be used to name an Excel file. In the final step, the break() macro exports the new data set into Excel. The problem is, in the file path, &grpdesc. does not update. So all Excel reports have the same name and just overwrite one another. I was able to validate that the grpdesc macro is updating with each macro run. It just doesn't update in the file path. Any ideas?
%macro break(byval, tblnm, report);
data &report&byval;
set &tblnm.(where=(uniqid= "&byval"));
run;
/*get group name for workbook*/
PROC SQL noprint;
SELECT DISTINCT description into :grpdesc
FROM &report&byval
;
QUIT;
PROC EXPORT DATA= &report&byval
DBMS=EXCEL REPLACE;
SHEET=&report;
RUN;
%mend;
%macro callbreak(tblnm, report);
data _null_ ;
set &tblnm.;
by uniqid;
if first.uniqid then
call execute( '%break('||uniqid||', &tblnm, &report )' );
run;
%mend callbreak;
%callbreak(Be_pct2, be);
Hard to tell if this will resolve everything ...
It looks like some of the generated code is waiting to run until after %CALLBREAK finishes. Force it to run earlier by sticking two lines just before the %MEND statement that ends %CALLBREAK:
data _null_;
run;
Let's see how close that gets to a solution.
Have you run the code with Options mprint symbolgen; on?
What a rat's nest of a problem! The basic issue you are running into is difficult to even explain. When CALL EXECUTE generates macro language statements, they execute immediately. So %BREAK executes right away, not waiting for the DATA _NULL_ step to finish. When CALL EXECUTE generates SAS code (DATA, SQL, EXPORT), those steps have to wait for the DATA _NULL_ step to finish before they can run. But as part of generating the EXPORT step, macro language attempts to resolve &GRPDESC. right away. Yuck!
A quick fix worth trying is to wrap the CALL EXECUTE string in %NRSTR. If it works, it will be the simplest fix:
call execute(%nrstr( '%break('||uniqid||', &tblnm, &report )' ));
Even then, there could be issues like how to represent quotes and a percent sign inside %NRSTR.
A second approach is a little more work, but highly likely to work. Replace the SQL/EXPORT logic. Instead of SQL, use a DATA step that uses a second CALL EXECUTE to generate the EXPORT step. Then you could use DESCRIPTION instead of &GRPDESC to generate the proper code.
Best of luck!
Thank you for taking the time to help. I used your second suggestion, and am encountering a new issue. When the new data _null_ step calls %export, it reports &byval is not resolved. &byval is used to create the data step without issue, and the &report identifier passes correctly. I don't know why &byval is not.
NOTE: Line generated by the CALL EXECUTE routine.
1 + PROC EXPORT DATA= be&byval) OUTFILE= "C:\Documents and
-
200
1 !+Settings\mmangi01\Desktop\dump\Hospital1.xlsx" DBMS=EXCEL REPLACE;
1 !+ SHEET=be; RUN;
ERROR 200-322: The symbol is not recognized and will be ignored.
%macro break(byval, tblnm, report);
data &report&byval;
set &tblnm.(where=(uniqid= "&byval"));
run;
data _null_;
set &report&byval;
by description;
if first.description then
call execute( '%export('||description||', &report&byval)' );
run;
%mend;
%macro callbreak(tblnm, report);
data _null_ ;
set &tblnm.;
by uniqid;
if first.uniqid then
call execute( '%break('||uniqid||', &tblnm, &report )' );
run;
%mend callbreak;
%callbreak(Be_pct2, be);
%macro export(grpdesc, tblnm);
PROC EXPORT DATA= &tblnm)
OUTFILE= "C:\Documents and Settings\mmangi01\Desktop\dump\&grpdesc. report.xlsx"
DBMS=EXCEL REPLACE;
SHEET=&report;
RUN;
%mend;
Hard to tell if this will resolve everything ...
It looks like some of the generated code is waiting to run until after %CALLBREAK finishes. Force it to run earlier by sticking two lines just before the %MEND statement that ends %CALLBREAK:
data _null_;
run;
Let's see how close that gets to a solution.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.