DATA Step, Macro, Functions and more

Passing macro parameters from a SAS dataset

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

Passing macro parameters from a SAS dataset

Hi all

 

I am currently using this code to execute a same macro with different parameters from a data set variable.

 

filename code temp;

 

data _null_;
set fll;
by sa_code;
file code;
put '%rarea ( snme = %sysfunc(strip( ' sa_code ' )));';
where flag = 1 and pflag NE 'C';
run;

 

%inc code/source2;

 

I would like to know if there is a better way of doing this or if there should be any additional considerations before taking this approach. I tried doing the same using call execute, but it was giving some strange macro resolution errors (for the macro variable that were created within the macro calls inside the rarea macro). So, in the essence of time I skipped call execute approach and settled with the file - put option.

 

My main concern is writing them to a file (temp file in this case) and then using it as include. Is there a possibility of that going wrong somewhere? I am also planning to remove source2 as it is printing lots of lines in the log. I am not sorting the fll data set beforehand using proc sort, but using by statement so that the macro parameters are always passed in the same order every time to the rarea macro. Is that a correct approach?

 

Thanks!


Accepted Solutions
Solution
‎12-16-2016 01:06 PM
Respected Advisor
Posts: 3,777

Re: Passing macro parameters from a SAS dataset

[ Edited ]

SASdn wrote:

Thanks!

 

When I was thinking between call execute vs file-put some time back, I digged in to a old thread where put was suggested instead of call execute by a senior member to keep things simple and also for the ease of looking the file in order to check if the macros have been called correctly. So I thought file-put is a equally good approach too! Initially I wanted to go with call execute too for the very reasons that you mentioned, but it did not work out and there was too much to dig into. Will wait for more inputs and see.

 

Regarding call execute issue.. Basically within rarea macro, five other macros (say %a,%b,%c,%d,%e) are being called upon. There are some into statements from SQL queries in %a - %e macros. Call execute was unable to resolve them for some strange reasons. %a - %e have no issues (running since long) when executed outside call execute or using this file-put method. Any thoughts? %a - %e have 100s of lines of codes and have no issues for certainity and it is not possible to touch or edit them in any ways. So the only problem was call execute executing them.


You problem sits on the edge of EXECUTE vs PUT as you are only writing one line.  I like PUT because it has features like NAMED put.  In my example I used the quote format to put quotes around your parameter argument but that my not be necessary for you.

 

With regard to you second paragraph that is the gotcha of EXECUTE it executes the MACRO when it is called but not the code the macro generates.  To delay execution until the end use %NRSTR(%rarea(.....

 

36         data _null_;
37            set fll;
38            by sa_code notsorted;
39            where flag = 1 and pflag NE 'C';
40            if first.sa_code;
41            *file code;
42            snme = sa_code;
43            put '%rarea(' snme =:quote32. ');';
44            run;

%rarea(snme="sa_code" );
%rarea(snme="decode" );

 

 

 

View solution in original post


All Replies
Super User
Posts: 5,256

Re: Passing macro parameters from a SAS dataset

If you use create file method chances are that you are overly complicating things.
Call execute works for most scenarios, but the information in this post is too scarce to tell me why it isn't working.
Data never sleeps
Contributor
Posts: 20

Re: Passing macro parameters from a SAS dataset

Thanks!

 

When I was thinking between call execute vs file-put some time back, I digged in to a old thread where put was suggested instead of call execute by a senior member to keep things simple and also for the ease of looking the file in order to check if the macros have been called correctly. So I thought file-put is a equally good approach too! Initially I wanted to go with call execute too for the very reasons that you mentioned, but it did not work out and there was too much to dig into. Will wait for more inputs and see.

 

Regarding call execute issue.. Basically within rarea macro, five other macros (say %a,%b,%c,%d,%e) are being called upon. There are some into statements from SQL queries in %a - %e macros. Call execute was unable to resolve them for some strange reasons. %a - %e have no issues (running since long) when executed outside call execute or using this file-put method. Any thoughts? %a - %e have 100s of lines of codes and have no issues for certainity and it is not possible to touch or edit them in any ways. So the only problem was call execute executing them.

Solution
‎12-16-2016 01:06 PM
Respected Advisor
Posts: 3,777

Re: Passing macro parameters from a SAS dataset

[ Edited ]

SASdn wrote:

Thanks!

 

When I was thinking between call execute vs file-put some time back, I digged in to a old thread where put was suggested instead of call execute by a senior member to keep things simple and also for the ease of looking the file in order to check if the macros have been called correctly. So I thought file-put is a equally good approach too! Initially I wanted to go with call execute too for the very reasons that you mentioned, but it did not work out and there was too much to dig into. Will wait for more inputs and see.

 

Regarding call execute issue.. Basically within rarea macro, five other macros (say %a,%b,%c,%d,%e) are being called upon. There are some into statements from SQL queries in %a - %e macros. Call execute was unable to resolve them for some strange reasons. %a - %e have no issues (running since long) when executed outside call execute or using this file-put method. Any thoughts? %a - %e have 100s of lines of codes and have no issues for certainity and it is not possible to touch or edit them in any ways. So the only problem was call execute executing them.


You problem sits on the edge of EXECUTE vs PUT as you are only writing one line.  I like PUT because it has features like NAMED put.  In my example I used the quote format to put quotes around your parameter argument but that my not be necessary for you.

 

With regard to you second paragraph that is the gotcha of EXECUTE it executes the MACRO when it is called but not the code the macro generates.  To delay execution until the end use %NRSTR(%rarea(.....

 

36         data _null_;
37            set fll;
38            by sa_code notsorted;
39            where flag = 1 and pflag NE 'C';
40            if first.sa_code;
41            *file code;
42            snme = sa_code;
43            put '%rarea(' snme =:quote32. ');';
44            run;

%rarea(snme="sa_code" );
%rarea(snme="decode" );

 

 

 

Contributor
Posts: 20

Re: Passing macro parameters from a SAS dataset

[ Edited ]

Wow, adding %nrstr solved the call execute issue. It worked like charm. thank you so much. 

 

Can you tell me what difference adding nrstr makes? Is it something to do with compilation vs execution phase macro resolution? 

 

Also is there any other scenario (apart from the one discussed above) where call execute could fail from executing the macros? Also is it okay to use by without sorting just to pass the parameters in alphabetical order to the macro?

Respected Advisor
Posts: 3,777

Re: Passing macro parameters from a SAS dataset


SASdn wrote:

Wow, adding %nrstr solved the call execute issue. It worked like charm. thank you so much. 

 

Can you tell me what difference adding nrstr makes? Is it something to do with compilation vs execution phase macro resolution? 

 

Also is there any other scenario (apart from the one discussed above) where call execute could fail from executing the macros?


It has to do with the call executed MACRO being executed but any SAS code it generates being queued to run after the data step finishes. Most often the problem is when macro variables are created with SQL INTO or CALL SYMPUT.  References to these variables would not work as expected, if at all, because that SQL/DATA step was not executed.  It is harder to construct an example where you want CALL EXECUTE to work as it does by default,  that's another reason to prefer PUT/%INC.  There is also a limit to the amount of lines/statements that can be CALL EXECUTED but it is quite large and not often a problem.   

Contributor
Posts: 20

Re: Passing macro parameters from a SAS dataset

Very insightful and helpful. I am going with call execute for my case as I have a macro call to be made. Thank you so much!

Valued Guide
Posts: 505

Re: Passing macro parameters from a SAS dataset

SAS Forum: Copying all datasets in sashelp.class that have a specified prefix to work directory but stopping if child process has an error

This is a dumb example but demonstrates the flexibility of DOSUBL.

This algoritm has other applications beyond this dumb example;
Replacing 'call execute'?

inspired by
https://goo.gl/HrHBcV
https://communities.sas.com/t5/Base-SAS-Programming/Using-a-macro-to-loop-through-files-and-process-data-and-output/m-p/318992#M69958

HAVE
====

%let mem=MAC;

data have;
set sashelp.vtable
    (keep=libname memname where=(libname="SASHELP" and memname=:"&MEM"));
run;quit;

These datasets in sashelp (then begin with MAC)

      LIBNAME    MEMNAME

 1     SASHELP    MACRS10
 2     SASHELP    MACRS15
 3     SASHELP    MACRS20
 4     SASHELP    MACRS3
 5     SASHELP    MACRS5
 6     SASHELP    MACRS7


WANT  (want the process of copying to stop if any dataset has 0 obs)
====

      LIBNAME    MEMNAME

      WORK    WANT_MACRS10   * copy of sashelp.MACRS10 in work directory
      WORK    WANT_MACRS15
      WORK    WANT_MACRS20
      WORK    WANT_MACRS3
      WORK    WANT_MACRS5
      WORK    WANT_MACRS7

WORKING CODE

     rc=dosubl('....' return number of obs loaded into obss_address

FULL SIMPLE SOLUTION
====================

If SAS has fixed the 2013 reported issue around sending macro variables from the
child to the parent datastep, then you can use the simple solution.
Otherwise you will have to use the more complex one.
I am running an 9.4M2 hopefully it is fixed in later versions?

The advantage of dosubl is two way communication with a child process.
You can trap errors more easily and not even generate additional code.


The simple one

%symdel obs_copied ; /* just incase */
data _null_;
  set sashelp.vtable (where=(libname="SASHELP" and memname=:"MAC"));
  call symputx('memname',memname);     /* pass to child */
  rc=dosubl('
    proc sql;
      create table WANT_&memname as
      select *
      from  sashelp.&memname
    ;quit;
    %let obs_copied=&sqlobs;
    ');
  if "&obs_copied" = "0"  then stop; /* from child */
run;quit;



FULL COMPLEX SOLUTION
=====================
%symdel err obs_address ;
data _null_;
  retain obs_address 0;
  set sashelp.vtable (where=(libname="SASHELP" and memname=:"MAC"));
  call symputx('memname',memname);
  call symputx("obs_address",put(addrlong(obs_address),hex16.));
  rc=dosubl('
    proc sql;
      create table WANT_&memname as
      select *
      from   sashelp.&memname
    ;quit;
     data _null_;
        call pokelong(&sqlobs,"&obs_address"x,8);
     run;quit;
    ');
    put memname  obs_address=;
    if obs_address = 0 then stop;
run;quit;


NOTE: Table WORK.WANT_MACRS7 created, with 8 rows and 2 columns.

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              7453.53k
      OS Memory           27300.00k
      Timestamp           12/14/2016 08:52:41 AM
      Step Count                        430  Switch Count  0


SYMBOLGEN:  Macro variable SQLOBS resolves to 8
SYMBOLGEN:  Macro variable OBS_ADDRESS resolves to D8E18A0A00000000
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.01 seconds
      memory              7453.53k
      OS Memory           27300.00k
      Timestamp           12/14/2016 08:52:42 AM
      Step Count                        430  Switch Count  0


MACRS7 OBS_ADDRESS=8
NOTE: There were 6 observations read from the data set SASHELP.VTABLE.
      WHERE (libname='SASHELP') and (memname=:'MAC');
NOTE: DATA statement used (Total process time):
      real time           1.64 seconds
      user cpu time       0.43 seconds
      system cpu time     0.90 seconds
      memory              7453.53k
      OS Memory           27300.00k
      Timestamp           12/14/2016 08:52:42 AM
      Step Count                        430  Switch Count  12

2905!     quit;






Valued Guide
Posts: 505

Re: Passing macro parameters from a SAS dataset

You can call a macro in DOSUBL
Valued Guide
Posts: 505

Re: Passing macro parameters from a SAS dataset

 

SAS Forum: Copying all datasets in sashelp.class that have a specified prefix to work directory but stopping if child process has an error

This is a dumb example but demonstrates the flexibility of DOSUBL.

This algoritm has other applications beyond this dumb example;
Replacing 'call execute'?

inspired by
https://goo.gl/HrHBcV
https://communities.sas.com/t5/Base-SAS-Programming/Using-a-macro-to-loop-through-files-and-process-data-and-output/m-p/318992#M69958

HAVE
====

%let mem=MAC;

data have;
set sashelp.vtable
    (keep=libname memname where=(libname="SASHELP" and memname=:"&MEM"));
run;quit;

These datasets in sashelp (then begin with MAC)

      LIBNAME    MEMNAME

 1     SASHELP    MACRS10
 2     SASHELP    MACRS15
 3     SASHELP    MACRS20
 4     SASHELP    MACRS3
 5     SASHELP    MACRS5
 6     SASHELP    MACRS7


WANT  (want the process of copying to stop if any dataset has 0 obs)
====

      LIBNAME    MEMNAME

      WORK    WANT_MACRS10   * copy of sashelp.MACRS10 in work directory
      WORK    WANT_MACRS15
      WORK    WANT_MACRS20
      WORK    WANT_MACRS3
      WORK    WANT_MACRS5
      WORK    WANT_MACRS7

WORKING CODE

     rc=dosubl('....' return number of obs loaded into obss_address

FULL SIMPLE SOLUTION
====================

If SAS has fixed the 2013 reported issue around sending macro variables from the
child to the parent datastep, then you can use the simple solution.
Otherwise you will have to use the more complex one.
I am running an 9.4M2 hopefully it is fixed in later versions?

The advantage of dosubl is two way communication with a child process.
You can trap errors more easily and not even generate additional code.


The simple one

%symdel obs_copied ; /* just incase */
data _null_;
  set sashelp.vtable (where=(libname="SASHELP" and memname=:"MAC"));
  call symputx('memname',memname);     /* pass to child */
  rc=dosubl('
    proc sql;
      create table WANT_&memname as
      select *
      from  sashelp.&memname
    ;quit;
    %let obs_copied=&sqlobs;
    ');
  if "&obs_copied" = "0"  then stop; /* from child */
run;quit;



FULL COMPLEX SOLUTION
=====================
%symdel err obs_address ;
data _null_;
  retain obs_address 0;
  set sashelp.vtable (where=(libname="SASHELP" and memname=:"MAC"));
  call symputx('memname',memname);
  call symputx("obs_address",put(addrlong(obs_address),hex16.));
  rc=dosubl('
    proc sql;
      create table WANT_&memname as
      select *
      from   sashelp.&memname
    ;quit;
     data _null_;
        call pokelong(&sqlobs,"&obs_address"x,8);
     run;quit;
    ');
    put memname  obs_address=;
    if obs_address = 0 then stop;
run;quit;


NOTE: Table WORK.WANT_MACRS7 created, with 8 rows and 2 columns.

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              7453.53k
      OS Memory           27300.00k
      Timestamp           12/14/2016 08:52:41 AM
      Step Count                        430  Switch Count  0


SYMBOLGEN:  Macro variable SQLOBS resolves to 8
SYMBOLGEN:  Macro variable OBS_ADDRESS resolves to D8E18A0A00000000
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.01 seconds
      memory              7453.53k
      OS Memory           27300.00k
      Timestamp           12/14/2016 08:52:42 AM
      Step Count                        430  Switch Count  0


MACRS7 OBS_ADDRESS=8
NOTE: There were 6 observations read from the data set SASHELP.VTABLE.
      WHERE (libname='SASHELP') and (memname=:'MAC');
NOTE: DATA statement used (Total process time):
      real time           1.64 seconds
      user cpu time       0.43 seconds
      system cpu time     0.90 seconds
      memory              7453.53k
      OS Memory           27300.00k
      Timestamp           12/14/2016 08:52:42 AM
      Step Count                        430  Switch Count  12

2905!     quit;






 

Super User
Posts: 10,500

Re: Passing macro parameters from a SAS dataset


SASdn wrote:

Hi all

 

I am currently using this code to execute a same macro with different parameters from a data set variable.

<removed for brevity> 

 

I tried doing the same using call execute, but it was giving some strange macro resolution errors (for the macro variable that were created within the macro calls inside the rarea macro).


Let me guess: Your Call execute looked like call execute("%rarea(snme="||SA_code||");");

So SAS tried to resolve the %rarea macro during execution of the data step.

Fix: This is one of the times NOT to use double quotes. Then the macro calls will be sent to the processor after the data _null_ step executes as intended.

 

 

Contributor
Posts: 20

Re: Passing macro parameters from a SAS dataset

Sorry, I didn't get that. Should I use the call execute with single quotes or skip having quotes at all?
Super User
Posts: 10,500

Re: Passing macro parameters from a SAS dataset

The argument to Call execute is string.

It could be a string variable, which is what is usually do for the parts involving dataset variables to have better control over combining things than ||. Either creating a string variable or using inline operators the parts that involve macro calls need to be inside single quotes.

☑ This topic is SOLVED.

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

Discussion stats
  • 12 replies
  • 376 views
  • 0 likes
  • 5 in conversation