BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ykk
Obsidian | Level 7 ykk
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
jakarman
Barite | Level 11

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

10 REPLIES 10
SASKiwi
PROC Star

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.

ykk
Obsidian | Level 7 ykk
Obsidian | Level 7

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.

Tom
Super User Tom
Super User

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;

Kurt_Bremser
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Loko
Barite | Level 11

Hello,

You can use the following macro statements:

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

%symdel cnt1;

%put deleted macro variable cnt1;

%end;

jakarman
Barite | Level 11

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 --<-----
ykk
Obsidian | Level 7 ykk
Obsidian | Level 7

Hi Jaap

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

ykk
Obsidian | Level 7 ykk
Obsidian | Level 7

Hi Jaap

Thanks a lot dosubl works for me.

SASKiwi
PROC Star

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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