topic Re: Reset SAS Macro Global Variable in SAS Enterprise Guide

Reset SAS Macro Global Variable

ykk — Thu, 22 Jan 2015 07:18:16 GMT

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.

Re: Reset SAS Macro Global Variable

SASKiwi — Thu, 22 Jan 2015 07:44:41 GMT

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.

Re: Reset SAS Macro Global Variable

Kurt_Bremser — Thu, 22 Jan 2015 08:01:09 GMT

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.

Re: Reset SAS Macro Global Variable

RW9 — Thu, 22 Jan 2015 09:28:51 GMT

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.

Re: Reset SAS Macro Global Variable

Loko — Thu, 22 Jan 2015 13:03:35 GMT

Hello,

You can use the following macro statements:

%if %symexist(cnt1) %then %do;

%symdel cnt1;

%put deleted macro variable cnt1;

%end;

Re: Reset SAS Macro Global Variable

ykk — Thu, 22 Jan 2015 18:33:47 GMT

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.

Re: Reset SAS Macro Global Variable

jakarman — Thu, 22 Jan 2015 19:03:18 GMT

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)

Re: Reset SAS Macro Global Variable

SASKiwi — Thu, 22 Jan 2015 19:07:50 GMT

Jaap sums it up pretty well. If this is not clear please let us know.

Re: Reset SAS Macro Global Variable

ykk — Thu, 22 Jan 2015 19:11:37 GMT

Hi Jaap

Is there any workaround for 9.3.we are using the sas version 9.3

Re: Reset SAS Macro Global Variable

ykk — Thu, 22 Jan 2015 19:44:56 GMT

Hi Jaap

Thanks a lot dosubl works for me.

Re: Reset SAS Macro Global Variable

Tom — Sat, 24 Jan 2015 00:17:08 GMT

Wow!

Why not just merge the tables?

data B ;

input rate_assignment_name $ ;

cards;

;;;;

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;

;;;;

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;