Desktop productivity for business analysts and programmers

Reset SAS Macro Global Variable

Accepted Solution Solved
Reply
Contributor ykk
Contributor
Posts: 24
Accepted Solution

Reset SAS Macro Global Variable

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.


Accepted Solutions
Solution
‎01-22-2015 02:03 PM
Valued Guide
Posts: 3,208

Re: Reset SAS Macro Global Variable

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)

---->-- ja karman --<-----

View solution in original post


All Replies
Super User
Posts: 3,233

Re: Reset SAS Macro Global Variable

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.

Contributor ykk
Contributor
Posts: 24

Re: Reset SAS Macro Global Variable

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.

Super User
Super User
Posts: 6,842

Re: Reset SAS Macro Global Variable

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;

Super User
Posts: 7,399

Re: Reset SAS Macro Global Variable

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,694

Re: Reset SAS Macro Global Variable

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.

Super Contributor
Posts: 308

Re: Reset SAS Macro Global Variable

Hello,

You can use the following macro statements:

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

%symdel cnt1;

%put deleted macro variable cnt1;

%end;

Solution
‎01-22-2015 02:03 PM
Valued Guide
Posts: 3,208

Re: Reset SAS Macro Global Variable

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)

---->-- ja karman --<-----
Contributor ykk
Contributor
Posts: 24

Re: Reset SAS Macro Global Variable

Hi Jaap

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

Contributor ykk
Contributor
Posts: 24

Re: Reset SAS Macro Global Variable

Hi Jaap

Thanks a lot dosubl works for me.

Super User
Posts: 3,233

Re: Reset SAS Macro Global Variable

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 1224 views
  • 0 likes
  • 7 in conversation