Help Needed with Call Execute and Nested Macros

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

Help Needed with Call Execute and Nested Macros

I've got a dataset of users that I'm trying to run build out individual reports on but I can't get the order in such a way that the individual datasets are properly created prior to the check for empty datasets/print statements.  I've tried reordering but I'm sure it has something to do with my nested macros being used from a call execute.  Any help anyone could provide would be greatly appreciated:

%macro breakout(tablename1,metric1);

/**********Import User Dataset**********/

proc import datafile="C:\SASReports\userfile.csv"

     out=userdataset

     dbms=csv replace;

run;

/********Call Macro To Create Individual User Datasets***********/

data _null_;                                                                                                                           

  set userdataset;                                                                          

  call execute('%individualdataset('||strip(lname)||')');                                                                    

run;

%mend

%macro individualdataset(lname);

                                                                            

/****************Create Dataset Per User************************/

proc sql;

create table &lname.&tablename1 as

select * from &tablename1

where Lead_Reviewer = %unquote(%str(%')&lname.%str(%'));    

quit; 

/**********Call Macro to Check to Make Sure Dataset Isn't Empty and Print Results************/

%checkobs(&lname.&tablename1.,&metric1)

%mend ; 

%macro checkobs(tablename,metric);

%let dsid=%sysfunc(open(&tablename.)); 

%let nobs=%sysfunc(attrn(&dsid,nobs));                                                                                               

%let rc=%sysfunc(close(&dsid));   

/***************If there are matching records then:*****************/                                                                                                                   

  %if &nobs>0 %then %do;                                                                                                               

/***Proc Print Statements****/

/*****************Print Means Databset (Only Mean Field)*****************/

proc print noobs data= &tablename;

var username field1 field2;

quit;

  %end;

/*********************If no matching records, print empty statement**************************/

  %else %do;     

data x;

x=1;

run;

proc sql;                                             

      select 'No Entries For This Timeframe'

        from x;

    quit; 

  %end;   

%mend;  


Accepted Solutions
Solution
‎05-14-2013 06:54 AM
Super User
Super User
Posts: 6,499

Re: Help Needed with Call Execute and Nested Macros

You can try using %NRSTR() to delay the execution of the macro so that it is not called during the process of CALL EXECUTE putting the commands onto the stack to execute, but ....

I find it much easier to generate the code into a file that can be called with %INCLUDE rather than mess with such things.

/********Call Macro To Create Individual User Datasets***********/

filename code temp;

data _null_;

  set userdataset;

  file code;

  put '%individualdataset(' lname  ');' ;

run;

%include code ;



View solution in original post


All Replies
Super User
Super User
Posts: 6,499

Re: Help Needed with Call Execute and Nested Macros

Check that the values of LNAME in your source data are valid to use as part of a table name?  What if someone's last name had a space or single quote in it?  lname="O'Hanlon" ?

Try simplifying things.

1) Add a semi-colon after the call to CHECKOBS just to make sure that SAS knows it it done. And extra semi-colon here will not hurt.

2) Not sure why you are using such a complex way to reference the value of &LNAME in macro INDIVIDUALDATASET.  Why not just "&LNAME" ?

3) Your CHECKOBS macro doesn't need any macro logic.  Perhaps the open and close of &TABLENAME is happening at the wrong time?  (Note: It also doesn't reference its second parameter).

%macro checkobs(tablename,metric);

* Use proc print if any obs ;

proc print noobs width=min data= &tablename;

  var username field1 field2;

quit;

* Output a page when there are no records. ;

data _null_;

  if nobs then stop ;

  file print titles footnotes;

  put 'No Entries For This Timeframe' ;

  set &tablename nobs=nobs;

run;

%mend checkobs;


4) You could just eliminate CHECKOBS as INDIVIDUALDATASETS macro will already know if it found any records because PROC SQL will set the automatic macro variable SQLOBS..

Occasional Contributor
Posts: 14

Re: Help Needed with Call Execute and Nested Macros

What I'm finding by looking at the log is that those individual datasets are correctly being created but AFTER the checkobs is executed.  I'm assuming this has something to do with the Call Execute command?  If I run this, it will not correctly run the checkobs but if I run it again, the datasets will be accurately read from the previous run and everything goes smoothly.

The reason I'm running this is because I actually have a few other calculations that are run within checkobs but I've tracked down the problem to the code inserted here.

SAS Employee
Posts: 23

Re: Help Needed with Call Execute and Nested Macros

Hi!

If the Call Execute contains a macro call, this will execute immediately. In the macro, all sas code will be put on the input stack, but macro stuff will execute immediately. Use %Nrstr to delay the macro call in Call Execute. I hope that the following code will explain this behaviour:

/***********************/
/*The "wrong" order*/


%macro MyMacro;
  %put 2. First time in MyMacro;
  data _null_;
    put "5. After the data step with call execute";
  run;
  %put 3. Second time in MyMacro;
%mend;

data _null_;
  put "1. In the data step";
  call execute('%MyMacro');
  put "4. After Call Execute";
run;

/************************/
/*The "correct" order*/


%macro MyMacro;
  %put 3. First time in MyMacro;
  data _null_;
    put "4. Data step in MyMacro";
  run;
  %put 5. After data step in MyMacro;
%mend;

data _null_;
  put "1. In the data step";
  call execute('%nrstr(%MyMacro)');
  put "2. After Call Execute";
run;

Occasional Contributor
Posts: 14

Re: Help Needed with Call Execute and Nested Macros

Yes, I've give that a whirl but the problem seems to be that since the macros are nested, the nrstr doesn't kick in for the 2nd checkobs macro, only the initial user dataset creation macro.

SAS Employee
Posts: 23

Re: Help Needed with Call Execute and Nested Macros

Hi!

I don't think that nesting is the problem. If you add %nrstr to your Call Execute in the Breakout macro,  the calls to %individualdataset should work.

In each call to %individualdataset, a proc sql will create a dataset, and if you substitute the call to %checkobs with a

%put %sysfunc(exist(&lname.&tablename1));

then we should see that at least this part works. If it works, we can continue our search for errors.

My suggestion is that you turn on mlogic and mprint with

options mlogic mprint;

Now it's easier to follow the process in the log. Also, I can see that you are missing a semicolon after %mend in the breakout macro.

Solution
‎05-14-2013 06:54 AM
Super User
Super User
Posts: 6,499

Re: Help Needed with Call Execute and Nested Macros

You can try using %NRSTR() to delay the execution of the macro so that it is not called during the process of CALL EXECUTE putting the commands onto the stack to execute, but ....

I find it much easier to generate the code into a file that can be called with %INCLUDE rather than mess with such things.

/********Call Macro To Create Individual User Datasets***********/

filename code temp;

data _null_;

  set userdataset;

  file code;

  put '%individualdataset(' lname  ');' ;

run;

%include code ;



SAS Employee
Posts: 23

Re: Help Needed with Call Execute and Nested Macros

Well... if you are not comfortable with Call Execute you should not use it.... but it's beautiful, and the code is simple if you know how to use it:

data _null_;

  set userdataset;

  call execute('%nrstr(%individualdataset('!!lname!!'))');

run;

Super User
Super User
Posts: 6,499

Re: Help Needed with Call Execute and Nested Macros

There are many reasons why generating the code using data step is easier than call execute.

  1. No worry about timing of execution.
  2. No worry about macro quoting.
  3. Easier to review the generated code to debug the syntax that you want to generate.
  4. Much easier to generate code when you can use the functionality of the SAS report writing. 

If you have a trivial piece of code to CALL EXECUTE then I would use it, but once you start using macro calls and especially if you have to resort to using macro quoting in the generated statements it quickly becomes much harder to visualize and implement without mistakes.

SAS Employee
Posts: 23

Re: Help Needed with Call Execute and Nested Macros

There is nothing wrong with generating code using a data step with File and Put. If you are more comfortable with this technique, then of course you should use it. I am not criticising this way of coding. Most of the time this is much easier, and also you can look at the generated code before %include. It's easier to search for errors.

BUT... the macro language is not a horrible monster, it's our friend. Don't be scared of macro quoting. Call Execute and the Macro language are tools that we have in the Sas toolbox, so why should we not use it? Of course, you should know what you are doing. Macro quoting is not for beginners, but I hardly think that Jeff is a beginner.

Occasional Contributor
Posts: 14

Re: Help Needed with Call Execute and Nested Macros

Hey all, I really appreciate it.  Due to time constraints I went with Tom's %include which allowed me to not have to worry about the call execute timing. 

I did try the %put %sysfunc(exist(&lname.&tablename1)); and it was returning 0.  Certainly, I prefer to investigate why something like this would not work correctly when at least on paper the nested macros look like they should work when using the %nstr but the backup on the report requests was growing too long.  In the end, the users wants their accurate file, they never care how elegant the code behind it is :-)  

After I get these out, I'll go back and see if I can make it more efficient.  As of now it take about 7 minutes to run the full thing and I'm sure I can cut that down.

Again, thanks for all the help!

☑ This topic is SOLVED.

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

Discussion stats
  • 10 replies
  • 832 views
  • 3 likes
  • 3 in conversation