Update macro symbol table inside data step

[PJB]

Hi all,

I would like to update the macro symbol table inside a data step. Specifically, something like this:

%MACRO TEST(name);
   rc = dosubl("
               data _null_;
                  set sashelp.class (where=(name eq '&name'));
                  call symputx('sex', sex,  'l');
               run;
         ");
   %put &sex;
%MEND;

data _null_;
   %TEST(Alfred);
run;

While running %TEST I would need any macro variable created by DOSUBL (or perhaps another method?) available in the (local) symbol table without halting the _null_ data step, I do not want this in a data step variable. From reading http://support.sas.com/kb/53/059.html it appears that this may not be possible? Or is there another way?

[Reeza]

By default it would be loaded to the local table, but as soon as the macro ends, the local table is gone so not sure how that's useful. 

---

@PJB wrote:

Hi all,

I would like to update the macro symbol table inside a data step. Specifically, something like this:

%MACRO TEST(name);
   rc = dosubl("
               data _null_;
                  set sashelp.class (where=(name eq '&name'));
                  call symputx('sex', sex,  'l');
               run;
         ");
   %put &sex;
%MEND;

data _null_;
   %TEST(Alfred);
run;

While running %TEST I would need any macro variable created by DOSUBL (or perhaps another method?) available in the (local) symbol table without halting the _null_ data step, I do not want this in a data step variable. From reading http://support.sas.com/kb/53/059.html it appears that this may not be possible? Or is there another way?

---

[PJB]

The point would be to do other things that the %put to these macro variables to return dynamic data step statements. The problem is that the macro variables are *not* loaded into the local symbol table. If I execute the code I posted I get the following behaviour:

• In the first call &sex gives a 'reference not resolved' warning. When the data step finishes however the macro variable is written to the GLOBAL symbol table (although the call symputx was specifically to local). You can %put &sex in open code and you'll get "M".
• All next calls will reference this global variable, which is no longer modified by the macro. Running %TEST(Carol) in subsequent data steps will always still return Alfred's "M".
• If I make call symputx address the global symbol table the reference will remain to the value returned from the previous data step, but at least now it can be updated in the global symbol table. That is, if I then add %TEST(Carol) twice in a data step each, the first will return Alfred's "M", the second "F" (which was set by the first %TEST(Carol) call).

Would there be a way to have access to the value for &sex in the current data step?

[ballardw]

Run your code with OPTION MPRINT to see what statements you are generating.

[Reeza]

Is this closer? You can also store it in a variable as shown, just to demonstrate that it's available.

%MACRO TEST(name);
   rc = dosubl("
                  set sashelp.class (where=(name eq ""&name""));
                  call symputx('sex', sex,  'l');
         ");
   %put &sex;
%MEND;

options mprint;
data _null_;
  x=resolve('%TEST(Alfred)');
  y=symget('sex');
  put 'Y=' y;
run;

[ChrisNZ · Accepted solution]

dosubl() creates another SAS instance which disappears after the call ends.

Its legacy are global macro variables, data sets, formats, maybe more I haven't really dug deep yet.

Note how these can be used:

data _null_;
  * Create format and table T1;
  RC= dosubl("proc format;value $A2Z 'A'='Z';run;data T1;A='A';put '1-' A $a2z.;run;");
  * New format is usable with putc but not with put (needs to be there at compile time);
  A='A'; B=put(A,$a2z.); C=putc(A,'$a2z.'); put '2-' B= C=;
  * Table T1 is usable with open function;
  DSID=open('T1'); put DSID=; RC=close(DSID); 
  * Table T1 is usable with hash table;
  dcl hash T1(dataset:'T1');
  T1.definekey('A');
  T1.definedone();
  RC=T1.check();
  put RC=;
run;

1-Z
2-B=A C=Z
DSID=1
RC=0

So the local variable that you create in dosubl only lives for the duration of that dosubl.

This does what you want (if your need is not more complex):

%macro TEST(name);
   %local sex dsid rc;
   %let dsid=%sysfunc(open(SASHELP.CLASS(where=(NAME="&name"))));
   %if &dsid=0 %then %return;
   %put &=dsid ;
   %let rc=%sysfunc(fetch(&dsid));
   %let sex=%sysfunc(getvarc(&dsid,%sysfunc(varnum(&dsid,SEX))));
   %put &=name &=sex;
   %let rc=%sysfunc(close(&dsid));
%mend;

data _null_;
  set SASHELP.CLASS;
  call execute('%test('||NAME||');');
  stop;
run;

DSID=1

NAME=Alfred SEX=M

[PJB]

Thank you ChrisNZ, this seems to work for what I would like to do.

To provide some additional detail: I have a dataset that contains a column with variable names and a column with variable lengths, and I would like to use this to generate dynamic length statements, preferably in a single macro call (so no prior initialization required). This is why it is of no use to retrieve values into data step variables (they basically already are, and these cannot be used in length statements) or to include another data step boundary.

[FreelanceReinh]

---

@PJB wrote:

I have a dataset that contains a column with variable names and a column with variable lengths, and I would like to use this to generate dynamic length statements, preferably in a single macro call (so no prior initialization required).

---

Like this (assuming character variables only)?

data lengths;
input var :$32. len;
cards;
name   20
sex     6
newvar  8
;

proc sql noprint;
select catx(' ',var,'$',len) into :lenspecs separated by ' '
from lengths;
quit;

data want;
length &lenspecs;
set sashelp.class;
/* more code */
run;

[Tom]

---

@PJB wrote:

Thank you ChrisNZ, this seems to work for what I would like to do.

To provide some additional detail: I have a dataset that contains a column with variable names and a column with variable lengths, and I would like to use this to generate dynamic length statements, preferably in a single macro call (so no prior initialization required). This is why it is of no use to retrieve values into data step variables (they basically already are, and these cannot be used in length statements) or to include another data step boundary.

---

That is a well know use case and there is no need to use DOSUBL() for that. You need to KNOW what LENGTH statements you want to generate BEFORE the data step starts. This is because SAS compiles data steps before it starts running them.  So just generate the code and then include it in the data step definition.  If the list of variables is short you can just use PROC SQL to generate the code into a macro variable. If it is long for a single macro variable (64k bytes) then generate the code into a file and %INCLUDE it.

So if your metadata has VARNAME, TYPE and LENGTH you could generate LENGTH statement using code like this:

filename code temp;
data _null_;
  set variable_list ;
  put 'LENGTH ' varname @;
  if type='char' then put '$' @;
  put length ';' ;
run;
data want ;
  %include code / source2;
   ...
run;

[Quentin]

---

@PJB wrote:

Thank you ChrisNZ, this seems to work for what I would like to do.

To provide some additional detail: I have a dataset that contains a column with variable names and a column with variable lengths, and I would like to use this to generate dynamic length statements, preferably in a single macro call (so no prior initialization required). This is why it is of no use to retrieve values into data step variables (they basically already are, and these cannot be used in length statements) or to include another data step boundary.

---

That's a helpful description.  This sounds to me like you want a function-style macro.  Using @FreelanceReinh's test data and code, would you want a macro that reads the LENGTHS datasets and then generate the list of values for the length statement, e.g. :

data want ;
  length %MakeLengthList(data=lengths)  ;  
  set sashelp.class ;
  newvar='hi' ;
run ;

The cool thing about DOSUBL is that it can create such function-style macros that return code.  And the macros can run proc steps.  You need to use %SYSFUNC to call DOSUBL, so that the DOSUBL executes when the macro runs (before the data step has been compiled).  The macro looks like:

%macro MakeLengthList(data=);
  %local LengthList rc;

  %let rc=%sysfunc(dosubl(%nrstr(

    proc sql noprint;
    select catx(' ',var,'$',len) into :LengthList separated by ' '
    from &data;
    quit;

  )));

&LengthList /*return*/
%mend MakeLengthList;

Use like:

data lengths;
input var :$32. len;
cards;
name   20
sex     6
newvar  8
;

*see the list generated by the macro ;
%put %MakeLengthList(data=lengths) ;

data want ;
  length %MakeLengthList(data=lengths) ;
  set sashelp.class ;
  newvar='hi' ;
run ;

Rick Langston wrote the original paper on using DOSUBL for this purpose.  See the last example in https://support.sas.com/resources/papers/proceedings13/032-2013.pdf.  

[PJB]

Thank you, this is the solution to the approach I was trying.

[Quentin]

As you've written it, the macro %TEST will complete execution before the DOSUBL statement has run.  Because the DOSUBL doesn't run until the DATA step executes.  Thus any macro variables created in the side session can only be returned to the main session global symbol table, because there is no local symbol table.  If you want to write to a local symbol table, the DOSUBL statement needs to execute inside the macro, e.g.:

%MACRO TEST(name) ;
  %local sex ;

  data _null_ ;
    rc = dosubl("
                data _null_ ;
                   set sashelp.class (where=(name eq '&name')) ;
                   call symputx('sex', sex) ; 
                run ;
          ");
    sex=symget("sex") ;
    put sex= ;
  run ;
 
  %put _local_ ;
%MEND;

%TEST(Alfred)

%TEST(Mary)

%TEST(Nope)

In the above code, the DOSUBL statement executes before the macro %TEST has completed execution.  Thus macro variable generated by in the DOSUBL session can be returned to the local symbol table of %TEST.

That said, the big picture of your question is not really clear.  It might help to provide more description regarding what you're trying to do (and why).

[ChrisNZ]

To weigh in on @Quentin's timing remark, here are attempts to output the value of the macro variable:

%MACRO TEST(name);
  rc=dosubl("data _null_;
               set sashelp.class(where=(name='&name'));
               call symputx('sex', sex, 'l');
               a= symget('sex');
               put '1-' A;
               call execute('%nrstr(%put 2-&sex;)');
               call execute('%put 3-%superq(sex);');
             run;
             %put 4-%superq(sex);");
  %put 5-%superq(sex);
  rc = dosubl('%put 6-&sex;');    
%MEND;

data _null_;
  %TEST(Alfred);
run;
%put 7-&sex;  

3-
4-

5-
1-M
2-M
6-M
7-M

As you can see the macro code inside the macro (outputs 3, 4, 5) runs before anything else is executed.

After symput has run, symget and the subsequent calls are successful retrieving the value.

Note the difference between 2 and 3 where we delay the execution of 2.

[Tom]

Can you explain a situation where there is some value in doing that?

Also can you create an example that is actually running inside of a macro so that there is a local symbol table that the data step could access?

[mkeintz]

You need a step delimiter between the RC=dosubl and the %put statement:

%MACRO TEST(name);
   rc = dosubl("
               data _null_;
                  set sashelp.class (where=(name eq '&name'));
                  call symputx('sex', sex,  'l');
               run;
         ");
   run;
   %put &=sex;
%MEND;

data _null_;
   %TEST(Alfred);
run;

Apparently the run inside the dosubl is not enough for SAS to recognize that the %put statement has to wait for the previous code to execute. 

edited addition: Or does this suggestion defeat what you intended to do?