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.
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.
Show us the code for %dqcreadin, and show us the main code where the macro is called.
%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;
%end;
%end;
%mend;
%missblfl;
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.