topic Re: how to update a macro variable in a call execute in SAS Programming

how to update a macro variable in a call execute

acordes — Thu, 21 Sep 2023 14:26:13 GMT

I want to delete the selected variable dynamically from the macro variable that was set at the beginning.

I put the code for context, it won't run like this.

Imagine I start with the macro variable having the value : var1 var2 var3 var4

Now I select var2 from the glmselect and I want to withdraw it form the macro variable so that it becomes : var1 var3 var4.

data matched;
format _from _to $32. r2 8.3;
output;
run;

data matched;
set matched(obs=0);
run;

proc sql;
select name into :vary separated by ' ' 
from nok where type=1;
quit;

%put &vary.;


data _null_;
set WORK.NAMY_OK(obs=10 where=(type=1 and ^index(lowcase(name), 'var') ));
call execute ('
ods output ModelInfo=model SelectionSummary=selsum;
proc glmselect data=public.ttt ;
model ' || name || '  = &vary. / selection=stepwise( stop=1 ) noint;
run;

proc sql;
select tranwrd("&vary.", strip(EffectEntered),"") into :vary 
from WORK.SELSUM;
quit;


%put &vary.;

proc sql;
insert into matched(_from, _to, r2 )
select ' || quote(name) || ', strip(EffectEntered), RSquare 
from  WORK.SELSUM;
quit;');
run;

Re: how to update a macro variable in a call execute

russt_sas — Thu, 21 Sep 2023 14:53:08 GMT

You could use TRANWRD to replace the string you want to remove with a blank, for example:

%let list=var1 var2 var3 var4;
%let cnt=%sysfunc(countw(&list));
%macro test(remove);
%let newlist=%cmpres(%sysfunc(tranwrd(&list,&remove,%str())));
%put &newlist;
%mend test;

/* Pass in the string you want to remove */

%test(var2)

Re: how to update a macro variable in a call execute

acordes — Thu, 21 Sep 2023 15:01:03 GMT

Thanks, I get the point, but I cannot adapt it to my needs.

I need the macro variable to be updated, not to create a new one.

Once a variable get selected I want to hide from being selected again.

Re: how to update a macro variable in a call execute

russt_sas — Thu, 21 Sep 2023 15:08:18 GMT

You do not have to create a new variable, that was just an example. Here is the same code using the same variable:

%let list=var1 var2 var3 var4;
%let cnt=%sysfunc(countw(&list));
%macro test(remove);
%let list=%cmpres(%sysfunc(tranwrd(&list,&remove,%str())));
%put &list;
%mend test;
%test(var2)

Re: how to update a macro variable in a call execute

Quentin — Thu, 21 Sep 2023 15:13:09 GMT

One potential gotcha I always forget is that despite the name, TRANWRD doesn't actually know about word boundaries. So if the word you delete is a substring of another word, it will delete the substring as well.

1    %let vary=AAA AAABBB BBB CCC ;
2    %let vary=%sysfunc(tranwrd(&vary,BBB,)) ;
3    %put &=vary ;
VARY=AAA AAA    CCC

You can add delimiters to the ends of the list to work around this, perhaps with something ugly like:

1    %let vary=AAA AAABBB BBB CCC ;
2    %let vary=%sysfunc(transtrn(%str( )&vary%str( ),%str( BBB ),%str( ))) ;
3    %put &=vary ;
VARY=AAA AAABBB CCC

Re: how to update a macro variable in a call execute

acordes — Thu, 21 Sep 2023 15:14:07 GMT

No, it does not work:


data matched;
format _from _to $32. r2 8.3;
output;
run;

data matched;
set matched(obs=0);
run;

proc sql;
select name into :vary separated by ' ' 
from nok where type=1;
quit;

%put &vary.;

%macro test(remove);
%let vary=%cmpres(%sysfunc(tranwrd(&vary,&remove,%str())));
%put &vary;
%mend test;

data _null_;
set WORK.NAMY_OK(obs=10 where=(type=1 and ^index(lowcase(name), 'var') ));
call execute ('
ods output ModelInfo=model SelectionSummary=selsum;
proc glmselect data=public.ttt ;
model ' || name || '  = &vary. / selection=stepwise( stop=1 ) noint;
run;

proc sql;
select strip(EffectEntered) into :oks 
from WORK.SELSUM;
quit;


%test(&oks.);

proc sql;
insert into matched(_from, _to, r2 )
select ' || quote(name) || ', strip(EffectEntered), RSquare 
from  WORK.SELSUM;
quit;');
run;

Re: how to update a macro variable in a call execute

russt_sas — Thu, 21 Sep 2023 15:24:13 GMT

Can you share what the NOK data set looks like?

Re: how to update a macro variable in a call execute

Quentin — Thu, 21 Sep 2023 15:28:23 GMT

What does "doesn't work" mean?

There are some tricky timing issues with CALL EXECUTE when you use macro code. You should try adding %NRSTR to mask the macro symbols until after CALL EXECUTE has generated the code.

Like:


data _null_ ;
  call execute(
    '%nrstr('
    || '%let vars=name sex height ;'
    || 'proc print data=sashelp.class ; var &vars ; run ;'
    || '%let vars=%sysfunc(transtrn(%str( )&vars%str( ),%str( height ),%str( ))) ;'
    || 'proc print data=sashelp.class ; var &vars ; run ;'
    || ')' 
  ) ;
run ;

Re: how to update a macro variable in a call execute

acordes — Thu, 21 Sep 2023 15:53:31 GMT

here comes the code / idea applied on sashelp.cars.

I want to identify variables that despite come without label can be related to other known variables.

We have this situation quite often, this ugly flat files that sometimes change the variable order or loose the variable names.

data cars;
set sashelp.cars;
id=put(_n_, 4.);
keep id _numeric_;
run;

proc sql;
select name||"=var" || strip(put(monotonic(), 3.)) into :reny separated by ' ' 
from dictionary.columns where libname='WORK' and memname='CARS'
and name ne "id";
quit;

data unknown;
set cars;
rename &reny.;
run;

data unknown;
set unknown;
array numy(10) var1-var10;
var_rand=rand('integer', 1, 10);
if rand('uniform', 0, 1) le 0.05 then do;
numy(var_rand)= numy(var_rand)*0.9;
end;
drop var_rand;
run;

proc contents data=cars out=namy_ok;
run;

proc contents data=unknown out=nok;
run;

data together;
merge cars unknown;
by id;
run;


data matched;
format _from _to $32. r2 8.3;
output;
run;

data matched;
set matched(obs=0);
run;

proc sql;
select name into :vary separated by ' ' 
from nok where type=1;
quit;

%put &vary.;


data _null_;
set WORK.NAMY_OK(obs=10 where=(type=1 and ^index(lowcase(name), 'var') ));
call execute ('
ods output ModelInfo=model SelectionSummary=selsum;
proc glmselect data=together ;
model ' || name || '  = &vary. / selection=stepwise( stop=1 ) noint;
run;

proc sql;
select tranwrd("&vary.", strip(EffectEntered),"") into :vary 
from WORK.SELSUM;
quit;

%put &vary.;

proc sql;
insert into matched(_from, _to, r2 )
select ' || quote(name) || ', strip(EffectEntered), RSquare 
from  WORK.SELSUM;
quit;');
run;

Re: how to update a macro variable in a call execute

acordes — Thu, 21 Sep 2023 15:55:05 GMT

after every iteration over proc glmselect I want to purge the selected variable from the list of candidates.

Re: how to update a macro variable in a call execute

Tom — Thu, 21 Sep 2023 16:53:09 GMT

@acordes wrote:

after every iteration over proc glmselect I want to purge the selected variable from the list of candidates.

I don't understand how CALL EXECUTE() would be involved in solving such a problem.

CALL EXECUTE() lets you stack up some code to run after the current step (the one that is running the CALL EXECUTE() statement) has finished running.

Re: how to update a macro variable in a call execute

acordes — Thu, 21 Sep 2023 18:13:48 GMT

So what Do you propose instead?
I need to update and shorten the candidate list of variables by when they end up getting selected by glmselect.

Re: how to update a macro variable in a call execute

russt_sas — Thu, 21 Sep 2023 18:17:45 GMT

I am still not 100% sure what you need.

If you are wanting to run multiple glmselect on a different variable each time, what determines which variable we use and/or exclude?

If you had variables var1 var2 var3 var4, are you wanting to run the glmselect 4 times removing one of the variables each time?

Re: how to update a macro variable in a call execute

Tom — Thu, 21 Sep 2023 18:30:54 GMT

@acordes wrote:
So what Do you propose instead?
I need to update and shorten the candidate list of variables by when they end up getting selected by glmselect.

Are you trying to simulate a STEPWISE regression? Are you sure the PROC doesn't already support that? Have you confirmed with a statistician that the approach is sensible?

Do you want to just loop thru the list and take 9 variables each time? So create a macro with code like this to call your model with the N ways to eliminate one variable from a list of N variables.

%let list=A|B|C;
%do i=1 to %sysfunc(countw(&list,|));
  %let varlist=%sysfunc(tranwrd(|&list|,|%scan(&list,&i,|)|,|));
  %let varlist=%sysfunc(translate(&varlist,%str( ),|));
/*
run your model using the N-1 variables in &VARLIST
*/
%end;

If you want to start with a list of 10 variables and run some statistical test and then select one variable to remove and repeat until what? There is just one variable left? If then the loop might look more like:

%let list=A B C;
%do %while(%length(&list));
/*
run your model.
select the variable to eliminate into macro variable DROPVAR
*/
  %let list=%sysfunc(tranwrd(%str( &list ),%str( &dropvar ),%str( )));
%end;

Re: how to update a macro variable in a call execute

Reeza — Thu, 21 Sep 2023 18:52:08 GMT

DOSUBL instead of CALL EXECUTE which runs immediately.

Re: how to update a macro variable in a call execute

acordes — Thu, 21 Sep 2023 19:45:44 GMT

I’ll give that a try. I don’t need the statistical waterproof, it’s meant as a trick to cluster one by one variables that supposed to measure the same whereas one group of variables is duly labeled and has meaningful names while the others stem from a flat file import where labels weren’t transmitted and variable names are like var1-var50.

Therefore I use the glmselect modeling each by each the known variables trying to find its sibling forcing a regression with stop criteria 1 and skipping an intercept term.

I would say it gives a good result but I’m struggling to update the unknown variables list.

Re: how to update a macro variable in a call execute

acordes — Thu, 21 Sep 2023 19:47:18 GMT

I’ll try tomorrow and let you know

Re: how to update a macro variable in a call execute

acordes — Fri, 22 Sep 2023 07:45:37 GMT

Brilliant, So the final code that works look like:

I have to fix what @Quentin cautioned, tranwrd does not care about word bounderies, probably I'll go for regex.

data cars;
set sashelp.cars;
id=put(_n_, 4.);
keep id _numeric_;
run;

proc sql;
select name||"=var" || strip(put(monotonic(), 3.)) into :reny separated by ' ' 
from dictionary.columns where libname='WORK' and memname='CARS'
and name ne "id";
quit;

data unknown;
set cars;
rename &reny.;
run;

data unknown;
set unknown;
array numy(10) var1-var10;
var_rand=rand('integer', 1, 10);
if rand('uniform', 0, 1) le 0.05 then do;
numy(var_rand)= numy(var_rand)*0.9;
end;
drop var_rand;
run;

proc contents data=cars out=namy_ok;
run;

proc contents data=unknown out=nok;
run;

data together;
merge cars unknown;
by id;
run;


data matched;
format _from _to $32. r2 8.3;
output;
run;

data matched;
set matched(obs=0);
run;

proc sql;
select name into :vary separated by ' ' 
from nok where type=1;
quit;

%put &vary.;


data _null_;
set WORK.NAMY_OK(obs=10 where=(type=1 and ^index(lowcase(name), 'var') ));
rc=dosubl ('
ods output ModelInfo=model SelectionSummary=selsum;
proc glmselect data=together ;
model ' || name || '  = &vary. / selection=stepwise( stop=1 ) noint;
run;

proc sql;
select tranwrd("&vary.", strip(EffectEntered),"") into :vary 
from WORK.SELSUM;
quit;

%put &vary.;

proc sql;
insert into matched(_from, _to, r2 )
select ' || quote(name) || ', strip(EffectEntered), RSquare 
from  WORK.SELSUM;
quit;');
run;