I have a macro which has got a proc sql to get the count based on the parameter passed to it.
Below is the code
%macro ins(USER_RATE_ASSIGNMENT_NAME);
%global cnt1;
%let cnt1=;
proc sql noprint;
select count(*) into :cnt
from SASDEV.EDM_RATE_ASSIGNMENT where
RATE_ASSIGNMENT_NAME="&USER_RATE_ASSIGNMENT_NAME";
%let cnt1 = &cnt;
%put count &cnt1;
%mend ins;
This macro is being called inside a data step.Global Variable cnt1 is being used in the data step.
When I execute the program the value for the Global variable cnt1 are not being reset for every macro call it still holds the value from the first macro execution.
Is there a way to reset the variable and get the latest values.
That code generated by %ins of the resulting macro processing will run AFTER the processing of the datastep. You cannot retrieve those values in the datastep as they have not being run yet.
SAS(R) 9.2 Macro Language: Reference (execute statement)
With 9.4 you have SAS(R) 9.4 Functions and CALL Routines: Reference, Third Edition (dosubl) that executes during the datastep.
As macro vars normally are processed/solved before the datastep has run you are needing the SAS(R) 9.2 Macro Language: Reference (resolve function)
Your macro contains a procedure step that will only execute on a step boundary, not "inside a data step" as you describe. Please post the relevant code calling and using the macro so we can advise further.
Hi SASKiwi
here is the code
options noquotelenmax;
options nomlogic mprint nosymbolgen;
%macro ins(USER_RATE_ASSIGNMENT_NAME);
%global cnt1;
proc sql;
select count(*) into :cnt1
from SASDEV.EDM_RATE_ASSIGNMENT where
RATE_ASSIGNMENT_NAME="&USER_RATE_ASSIGNMENT_NAME";
%put count &cnt1;
%mend ins;
data STATIC.STATUS;
set STATIC.Manual_correction;
length STATUS $200;
if
not missing(USER_RATE_ASSIGNMENT_NAME)
then
do;
if not missing(USER_RATE_ASSIGNMENT_NAME) then
do;
call execute('%ins(USER_RATE_ASSIGNMENT_NAME = '||USER_RATE_ASSIGNMENT_NAME||')');
count=symget('cnt1');
if count=0 then
do;
RAerror='Enter Valid Rate Assignment Name';
end;
if count=1 then
do;
RAerror=' ';
end;
end;
%put &cnt;
%put count;
end;
run;
I am trying to check the rate_assignment_name count using the macro variable cnt but it is always showing '0' in the count.
Wow!
Why not just merge the tables?
data B ;
input rate_assignment_name $ ;
cards;
A
B
B
C
;;;;
data A;
input ID rate_assignment_name $ ;
cards;
1 A
2 B
3 D
;;;;
data want ;
merge A (in=in1) B (in=in2) ;
by rate_assignment_name ;
if in1 ;
if first.rate_assignment_name then cnt=0;
cnt + in2 ;
if last.rate_assignment_name then do;
if cnt=0 then RAerror='Enter Valid Rate Assignment Name';
else if cnt=1 then RAerror=' ';
else RAerror='Too many matches';
output;
end;
run;
You could join them using PROC SQL code:
proc sql noprint ;
create table STATIC.STATUS as
select a.*
, case when (b.rate_assignment_name is not null) then ' '
else 'Enter Valid Rate Assignment Name'
end as RAerror
from STATIC.Manual_correction a
left join SASDEV.EDM_RATE_ASSIGNMENT b
on a.rate_assignment_name = b.rate_assignment_name
;
quit;
If the lookup table has an index you could even count the matches in a data step.
data B (index=(rate_assignment_name));
input rate_assignment_name $ ;
cards;
A
B
B
C
;;;;
data A;
input ID rate_assignment_name $ ;
cards;
1 A
2 D
3 B
;;;;
data want ;
set A ;
do cnt=0 by 1 until (_iorc_) ;
set B key=rate_assignment_name ;
end;
_error_=0;
if cnt=0 then RAerror='Enter Valid Rate Assignment Name';
else if cnt=1 then RAerror=' ';
else RAerror='Too many matches';
run;
If you simply "call" the macro inside a data step, the proc sql statement in the macro will immediately end the data step compilation(!) phase and execute the data step, so you will only have the initial value of &cnt1 in the data step.
If you use call execute, the macro will execute immediately, but the code generated by the macro will only execute after the data step has finished, same result.
Post sample data of what you have, and what you want. I don't see any value in the code you post above, calling a macro, to run an sqlstep to create a macro variable just seems like going round and round in circles.
Hello,
You can use the following macro statements:
%if %symexist(cnt1) %then %do;
%symdel cnt1;
%put deleted macro variable cnt1;
%end;
That code generated by %ins of the resulting macro processing will run AFTER the processing of the datastep. You cannot retrieve those values in the datastep as they have not being run yet.
SAS(R) 9.2 Macro Language: Reference (execute statement)
With 9.4 you have SAS(R) 9.4 Functions and CALL Routines: Reference, Third Edition (dosubl) that executes during the datastep.
As macro vars normally are processed/solved before the datastep has run you are needing the SAS(R) 9.2 Macro Language: Reference (resolve function)
Hi Jaap
Is there any workaround for 9.3.we are using the sas version 9.3
Hi Jaap
Thanks a lot dosubl works for me.
Jaap sums it up pretty well. If this is not clear please let us know.
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!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.