Hi @Azeem112
Stealing some code from @PeterClemmensen response 😉, here is another approach
data have;
length ID $10. score 8. formula $200.;
infile datalines dsd missover ;
input ID $ score formula $;
datalines;
KRI1,,KRI2/KRI3
KRI2,1,
KRI3,10,
KRI4,,(KRI5-KRI6)*100
KRI5,13,
KRI6,16,
KRI7,,(KRI8+KRI9)/(KRI10-KRI11)
KRI8,13,
KRI9,16,
KRI10,1,
KRI11,10,
;
run;
/* Create custom informat that can be used to convert ID to matching numeric value */
data infmt;
retain fmtname 'ref' type 'I';
/* Exclude formula records */
set have(where=(missing(formula)) rename=(id=start score=label));
run;
proc format cntlin=work.infmt; run;
/* Use the custom Informat with Regular Expression */
data want(KEEP=ID formula score);
set have(where=(strip(formula) ne ''));
length formula2 $400;
formula2 = cats('%sysevalf(',prxchange('s/KRI(\d+)/%sysfunc(inputn(KRI$1,ref.))/',-1,formula),')');
score = input(resolve(formula2),best.);
run;
Hope this helps
... View more
