Thank you everyone for your responses and guiding me through this... In the end, I was able to make this work by creating the macro variables for the Key("Refcheck" here) and the Statcheck variables in the lookup dataset, merge the lookup with the main datasets and loop through the macro variables to apply the condition. data lookup; length RefCheck $25 StatCheck $65.; input RefCheck 1-16 StatCheck 17-80; *input RefCheck StatCheck $34.; *call symputx(RefCheck, Statcheck); datalines; REQUEST Stat="&Request." SHORTLABEL Stat="&Admin."||" T"||"&TM."||" "||"RFMT" IBISFORMCODE Stat="&MetaForm." FORMDESCRIPTION Stat="Meta Form" TOTAL input(Stat,8.) >0 MSTTOT input(Stat,8.) >0 MSTVAL input(Stat,8.) >0 QVDOM input(Stat,8.) >0 QVINT input(Stat,8.) >0 WDOM input(Stat,8.) >0 WINT input(Stat,8.) >0 SAMP:MSTVAL Stat="GEN"||" "||"&MetaForm."||" "||"&Admin."||" "||"120000001" SAMP:QVDOM Stat="GEN"||" "||"&MetaForm."||" "||"&Admin."||" "||"121000001" SAMP:QVINT Stat="GEN"||" "||"&MetaForm."||" "||"&Admin."||" "||"122000001" SAMP:WDOM Stat="GEN"||" "||"&MetaForm."||" "||"&Admin."||" "||"123000001" SAMP:WINT Stat="GEN"||" "||"&MetaForm."||" "||"&Admin."||" "||"124000001" ACCOMMFLAG stat="Missing" ADMINISTRATION stat="&Admin." DELMODE stat="MST" EXCLUDE stat="Missing" FLDSCRFLAG stat="Missing" GENAUTO stat="Yes" GENSKM stat="PROD" OPTIONLIB stat="G:\\fortnt" PROGRAM stat="GRE" Q1SECTTERM Stat in ("0", "3", "4", "5") Q2SECTTERM Stat in ("0", "3", "4", "5") RECTYPE Stat ="DTL" REGID Stat ="ERS" SIRB Stat ="Populated" SIRM Stat ="Populated" SCRIND Stat not in("C") SCRPHASE Stat in("RPTBL" "SCRD" "READYTOARCHIVE" "ARCHIVING" "COMPLETE") SESRSTRT input(Stat,8.) >0 or stat = "Missing" SESTERM Stat in("0", "3", "4", "5") SKMQNT01ANS input(Stat,8.) >=0 SKMQNT02ANS input(Stat,8.) >=0 SKMVBL01ANS input(Stat,8.) >=0 SKMVBL02ANS input(Stat,8.) >=0 SKMWRT00ARG input(Stat,8.) >=0 SKMWRT00ISS input(Stat,8.) >=0 SPARSEMATRIX input(Stat,8.) >=0 TEST Stat ="GEN" V1SECTTERM Stat in("0", "3", "4", "5") V2SECTTERM Stat in("0", "3", "4", "5") ; run; PROC SQL /*noprint*/ ; SELECT refcheck INTO :refcheck1 - :refcheck&SysMaxLong. FROM work.lookup; SELECT COUNT(DISTINCT refcheck) INTO :refcheckOBS FROM work.lookup; PROC SQL /*noprint*/ ; SELECT statcheck INTO :statcheck1 - :statcheck&SysMaxLong. FROM work.lookup; QUIT; proc sort data=Lookup;by RefCheck;run; proc sort data=PeReformat3; by RefCheck;run; %macro lookup; data PeReformat3 ; length RefCheck $25. name $8.; format RefCheck $25.; merge lookup (in=in1) PeReformat3(in=in2); by RefCheck; if in2; length valid $25.; valid="N/A"; if in1 and in2 then do; %do i=1 %to &refcheckobs.; if refcheck="&&refcheck&i.." then do; if &&statcheck&i.. then valid="Valid"; else valid="Not Valid"; end; %end; end; run; %mend lookup; %lookup;
... View more