topic Re: Array to uptade row where criteria is met in SAS Programming

Array to uptade row where criteria is met

ern_23 — Thu, 07 Mar 2024 06:58:08 GMT

Dear community,

With the following code I want to add new column to existing dataset by defining array and this array should add value if my criteria met. For instance suppose that I have record and this record has 1 for first and third columns. If value is 1 then I expect my array to add corresponding numeric value to this rules (i.e 20, 30 , what is defined in points table). However following code updates each matched column's value with correspondingfinal loop value (my array loop finishes at 5 and if Rule5's value is 100, than rest of the matched columns updated with 100). How to preserve each rule's own value and not to replace previous one's value with the next updated one?

data points(DROP= i);
do i=1 TO 10;
COL1 = CATS("Rule",i);
COL2 = 10 * i;
OUTPUT;
END;
RUN;

data base (drop=i);
do i=1 TO 5;
CL_NO = i;
output;
end;
run;

data base2 (drop=i);
do i=1 to 5;
CL_NO = i;
Rule1 = RAND("integer",0,1);
Rule2 = RAND("integer",0,1);
Rule3 = RAND("integer",0,1);
Rule4 = RAND("integer",0,1);
Rule5 = RAND("integer",0,1);
output;
end;
run;

proc sql;
create table base3 as
select a.CL_NO, Rule1, Rule2, Rule3,Rule4,Rule5
from base a left join base2 b
on a.cl_no = b.cl_no;
quit;

options mprint;
%macro main_macro();

%macro another_macro(inp_trig,inp_index);

data out_&inp_index.;
set points;
where COL1 = "&inp_trig.";
run;

%global puan_&inp_index.;

proc sql;
select col2
into : point_&inp_index.
from out_&inp_index.;
quit;

%mend;

DATA RESULT;
SET base3;
ARRAY RULE[*] Rule:;
ARRAY RESULT[5] RESULT1-RESULT5;

DO i=1 to 5;

temp_result = CATS("Rule",i);
call symputx("temp_result_macro",temp_result,"G");
call symputx("index",i,"G");

if RULE[i] = 1 then do;

call execute('%another_macro(&temp_result_macro,&index)');

result[i] = &&point_&index.;

end;

run;

%mend;

%main_macro;

Thank you

Re: Array to uptade row where criteria is met

Patrick — Thu, 07 Mar 2024 07:19:55 GMT

Really appreciated that you share code that creates sample data and includes what you've done so far.

I'm unfortunately still not fully following your explanation and it's for me too much uncommented code to try and work out/guess what you're trying to achieve.

What would help me is if you just would share code that creates your HAVE tables, a desired result (code that creates a WANT table or just grid with the desired values) and then an explanation how you intend to get from Have to Want.

Re: Array to uptade row where criteria is met

yabwon — Thu, 07 Mar 2024 07:38:59 GMT

Do you mean something like this:

Before:

Code:

data want;
  if 0 then set points;
  declare hash H (dataset:"points");
  H.DefineKey("COL1");
  H.DefineData("COL2");
  H.DefineDone();

  do until(EOF);
    set base3 end=EOF;

    array Rule Rule:;

    do over Rule;
      if Rule = 1 then 
        do;
          COL1 = vname(Rule);
          if  0 = H.find() then Rule = Col2;
        end;
    end;
    output;
    drop COL2 COL1;
  end;
stop;
run;

proc print data=want;
run;

After:

Bart

Re: Array to uptade row where criteria is met

ern_23 — Thu, 07 Mar 2024 08:04:56 GMT

Thank you, this is what I want. Just for wondering, in my macro code I am trying to catch and assign point of rule with the following statement;

if RULE[i] = 1 then do;

call execute('%another_macro(&temp_result_macro,&index)');

result[i] = &&point_&index.;

so I expect in each iteration, my Rule's point should assign into &point_1, &point_2 etc macro variables. This approach also works , however in the end of the iteration , it updates all rules that meet condition with the Rule5's point. Is there a way fix this issue in the macro and ensure each rule takes its own value ?

Re: Array to uptade row where criteria is met

yabwon — Thu, 07 Mar 2024 08:47:14 GMT

It doesn't seem to work.

When I run the macro I got the following in the log:

1    %main_macro;
MLOGIC(MAIN_MACRO):  Beginning execution.
MPRINT(MAIN_MACRO):   DATA RESULT;
MPRINT(MAIN_MACRO):   SET base3;
MPRINT(MAIN_MACRO):   ARRAY RULE[*] Rule:;
MPRINT(MAIN_MACRO):   ARRAY RESULT[5] RESULT1-RESULT5;
MPRINT(MAIN_MACRO):   DO i=1 to 5;
MPRINT(MAIN_MACRO):   temp_result = CATS("Rule",i);
MPRINT(MAIN_MACRO):   call symputx("temp_result_macro",temp_result,"G");
MPRINT(MAIN_MACRO):   call symputx("index",i,"G");
MPRINT(MAIN_MACRO):   if RULE[i] = 1 then do;
MPRINT(MAIN_MACRO):   call execute('%another_macro(&temp_result_macro,&index)');
SYMBOLGEN:  && resolves to &.
WARNING: Apparent symbolic reference INDEX not resolved.
SYMBOLGEN:  Unable to resolve the macro variable reference &index
NOTE: Line generated by the macro variable "INDEX".
1     &point_&
      -
      22
WARNING: Apparent symbolic reference POINT_ not resolved.
WARNING: Apparent symbolic reference INDEX not resolved.
NOTE 137-205: Line generated by the invoked macro "MAIN_MACRO".
3    ('%another_macro(&temp_result_macro,&index)');  result[i] = &&point_&index.;  end;  end;  run;
                                                                          ------
                                                                          22
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant,
              a datetime constant, a missing value, INPUT, PUT.

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant,
              a datetime constant, a missing value, arrayname, (, +, -, INPUT, NOT, PUT, ^, _NEW_, ~.

NOTE: Line generated by the invoked macro "MAIN_MACRO".
3    ('%another_macro(&temp_result_macro,&index)');  result[i] = &&point_&index.;  end;  end;  run;
                                                                          ------
                                                                          201
ERROR 201-322: The option is not recognized and will be ignored.

MPRINT(MAIN_MACRO):   result[i] = &point_& index.;
MPRINT(MAIN_MACRO):   end;
MPRINT(MAIN_MACRO):   end;
MPRINT(MAIN_MACRO):   run;

INFO: Character variables have defaulted to a length of 200 at the places given by: (Line):(Column).
      Truncation can result.
      2:65     temp_result
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.RESULT may be incomplete.  When this step was stopped there were 0
         observations and 14 variables.
WARNING: Data set WORK.RESULT was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


MLOGIC(MAIN_MACRO):  Ending execution.

Basically, the reason it does _not_ work is bad "timing" in the program logic.

The:

call execute('%another_macro(&temp_result_macro,&index)');

line executes after the:

result[i] = &&point_&index.;

line needs it.

Call Execute() executes long after you need its results, if you run:

data _null_;
  call execute('proc sql; select sum(age) into: S_A from sashelp.class; quit;');

  x = "&S_A.";
run;

the log will show warning about not resolved symbol in compilation phase of the data step, long before SQL was executed:

1    data _null_;
2      call execute('proc sql; select sum(age) into: S_A from sashelp.class; quit;');
3
4      x = "&S_A.";
WARNING: Apparent symbolic reference S_A not resolved.
5    run;

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


NOTE: CALL EXECUTE generated line.
1   + proc sql;
1   +           select sum(age) into: S_A from sashelp.class;
1   +                                                         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds

Proper timing when using macro language, if one does not have experience, can be hard.

If I can share an advice, despite my limitless affection to the macro language, a "golden rules" (which is a paraphrase of saying about regular expressions) which goes like this: "If you can equivalently do something with macro language and without it, do it without it." 🙂

Bart