BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mikemangini
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

4 REPLIES 4
ballardw
Super User

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

Astounding
PROC Star

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!

mikemangini
Obsidian | Level 7

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;

Astounding
PROC Star

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.

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
  • 4 replies
  • 1213 views
  • 3 likes
  • 3 in conversation