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;
end;
run;
%mend;
%main_macro;
Thank you
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.
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
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
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.