topic Re: Macro in file path not updating in SAS Programming

Macro in file path not updating

mikemangini — Fri, 19 Jun 2015 16:20:31 GMT

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

OUTFILE= "C:\Documents and Settings\mmangi01\Desktop\dump\&grpdesc. report.xlsx"

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);

Re: Macro in file path not updating

ballardw — Fri, 19 Jun 2015 16:37:32 GMT

Have you run the code with Options mprint symbolgen; on?

Re: Macro in file path not updating

Astounding — Fri, 19 Jun 2015 17:02:26 GMT

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!

Re: Macro in file path not updating

mikemangini — Fri, 19 Jun 2015 17:59:25 GMT

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;

Re: Macro in file path not updating

Astounding — Fri, 19 Jun 2015 18:33:22 GMT

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.