topic Re: Global macro variables in SAS Programming

Global macro variables

petlove — Wed, 18 Jul 2018 16:47:21 GMT

Hi,

I need to reset the global macro value before the next loop.

I am calling macro %dqcreadin which has macro variable: &variables (list of variables like ="&dsn.BLFL &dsn.ORRES"), &domain: list of datasets (e.g. LB#EG).

%dqcreadin;
%if %symexist(domain) %then %do;
%do i= 1 %to %eval(%sysfunc(count(&domain,#))+1);
%let dsn=%scan(&domain,&i,#);

When next loop starts for new dataset e.g. here EG: &variables should have values EGBLFL EGORRES. However, in my log it has values of previous loop that is LBBLFL LBORRES.

I tried using "%symdel Variables" in program before calling %dqcreadin. However, it still does have LBBLFL LBORRES.

Re: Global macro variables

PaigeMiller — Wed, 18 Jul 2018 16:50:53 GMT

Show us the code for %dqcreadin, and show us the main code where the macro is called.

Re: Global macro variables

petlove — Wed, 18 Jul 2018 17:12:30 GMT

%macro dqcreadin(id=);

%xls2sas(_infile=dqc.xlsx, _indir=../../../../../metadata, _sheet=Sheet1, _datarow=2, _outdata=dqcout);

data dqcout;
set dqcout(rename=(var1=id var3=title var6=variables var7=domain var8=issue var10=exception));
if issue='Yes';
if id="&id";
domain=tranwrd(domain,', ','#');
if index(variables,'--')>0 then variables=tranwrd(variables,'--',"&dsn.");
variables=tranwrd(variables,', ',' ');
dmvars =tranwrd(exception,',',' ');
keep id title variables domain issue dmvars ;
run;

%global id title variables domain dmvars;

proc sql;
select id, title , variables, domain, dmvars into :id, :title , :variables, :domain, :dmvars
from dqcout
;
quit;

%put &id &title &variables &domain &dmvars;

%mend dqcreadin;

Main code:

%macro missblfl;

%symdel variables;
%dqcreadin(id=0021);
%if %symexist(domain) %then %do;
%do i= 1 %to %eval(%sysfunc(count(&domain,#))+1);
%let dsn=%scan(&domain,&i,#);

%if %sysfunc(exist(&sdtm..&dsn)) %then %do;

data &dsn;
set &sdtm..&dsn (keep=usubjid &variables);
record=_n_;
run;

%end;

%mend;

%missblfl;

Re: Global macro variables

ballardw — Wed, 18 Jul 2018 18:06:09 GMT

Be advised that frequent use of the program structure like this:

data dqcout;
    set dqcout ;

Can lead to difficult debugging as destroy the original data set. So when attempting to trace back to values of things manipulated (such as your variable var6 / variables ). Especially since you just read an external file in this case AND manipulated the value of the problematic variable in general.

Re: Global macro variables

petlove — Wed, 18 Jul 2018 18:19:34 GMT

Thank you for advise. Will follow it.

Re: Global macro variables

petlove — Wed, 18 Jul 2018 20:02:31 GMT

I changed VARIABLE name in %dqcreadin macro. It resolved the issue. Now, it resets the &variables values for each loop.

Thank you for your help and suggestions.