DATA Step, Macro, Functions and more

Macro in file path not updating

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

Macro in file path not updating

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


Accepted Solutions
Solution
‎06-19-2015 02:33 PM
Super User
Posts: 5,518

Re: Macro in file path not updating

Posted in reply to mikemangini

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.

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Macro in file path not updating

Posted in reply to mikemangini

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

Super User
Posts: 5,518

Re: Macro in file path not updating

Posted in reply to mikemangini

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!

Contributor
Posts: 34

Re: Macro in file path not updating

Posted in reply to Astounding

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;

Solution
‎06-19-2015 02:33 PM
Super User
Posts: 5,518

Re: Macro in file path not updating

Posted in reply to mikemangini

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 300 views
  • 3 likes
  • 3 in conversation