How to lookup a varaible from one table and populate to another table with a condition

[SK4]

 I have to validate a variable based on the a look up table.  it could be a simple merge by the key variable however the lookup value is a conditional base.

I have a dataset with two variables, Check  (which match with the "Check" column in the lookup) and second column Stat. The value of the Stat should match with the ValidStat column in the lookup.  One problem I am having is that there is a variation in the conditional operators. For example the first two rows above are "=", row 3 is a "In" operator, and rows 4 and 5 are grater than, equal and/or missing.

Is there an easy way to do this without hardcoding too much and merging this lookup with my SAS dataset and check ValidStat against the Stat column.

here is example lookup table

check             Validstat

1.   key1           1

2.   key2            dog

3.   key3           2, 3, 4

4.   key4           ❤️  or missing

5.   key5            <= 5 or missing

6.   key6           missing

Dataset to validate:  (please note the "validate" column is the desired output)   

Check          Stat       Validate

1. Key1         1                Valid

2. Key2          cat            Invalid

3. Key3           5             Invalid

4. Key4          2              Valid

5. Key5          10           Invalid

6. Key6          x             Invalid

Many thanks

SK

[noling]

This doesn't work and I would appreciate it if anyone can explain the error. Note that I changed the checks to be the comparison and right hand side of an IF condition:

data _null_;
length key $5 check $50;
	input key check 7-50;
	call symputx(key, check);
datalines;
key1  =1
key2  ="dog"
key3  in(2, 3, 4)
key4  <3  or is missing
key5  <= 5 or is missing
key6  is missing
;;;
run;

%put _all_;
GLOBAL KEY1 =1
GLOBAL KEY2 ="dog"
GLOBAL KEY3 in(2, 3, 4)
GLOBAL KEY4 <3  or is missing
GLOBAL KEY5 <= 5 or is missing
GLOBAL KEY6 is missing

*then pull in the macros to preform the check;

data want;
	length key $5 stat $10 validate $10;
	input key stat;
	key=strip(key);

	*check against macro lookup;
	if (stat symget(key)) then validate ='Valid';
	else validate='Invalid';

datalines;
key1 1  
key2 cat
key3 5  
key4 2  
key5 10 
key6 x  
;;;;
run;

38 if (stat symget(key)) then validate ='Valid';
                ______ (under symget)
                22
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, /, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT,
LE, LT, MAX, MIN, NE, NG, NL, OR, [, ^=, {, |, ||, ~=.

Thanks!

[Shmuel]

@noling, to avoid the error you need define a macro code.

Here is an variation of the second step:

%macro chk(func);
    if (stat  &func.) then validate ='Valid';
	              else validate='Invalid'; 
%mend;

data want;
	length key $5 stat $10 validate $10;
	input key stat;
	key=strip(key);
	*check against macro lookup;
	%chk(&!!key!!.);  /* code not tested */
datalines;
key1 1  
key2 cat
key3 5  
key4 2  
key5 10 
key6 x  
;;;;
run; 

[andreas_lds]

The global-statement begins with % and you can't assignment a value with global, you need
%let varname = value;

In stat symget(key) a comparison operator is missing between "stat" and symget, the operator has to be there when the step is compiled, symget resolves during execution. Maybe the problem could be solved by using resolve function, can't look it up right now, sorry. But in general I can't recommend storing data in macro-variables.

@SK4: you could merge by key and and the use resolve to compute validate.

[SK4]

Hi Andreas_Ids or anyone else, can you show me how to use the Resolve function. I tried following the example in one of the links provided, however that example is resolving mathematical expression, different from what I am trying to achieve.  I also tried other examples on this board and could not get the results..

As you mentioned I did merge the dataset with by the key values so now I have a dataset that looks like this:  

Main dataset (snippet)

RefCheck	StatCheck	Stat
ACCOMMFLAG	Stat is missing
ADMINISTRATION	Stat ="&Admin."	11111111A
DATERUN	Stat ="&today."	10/22/2018
DELMODE	Stat ="MST"	MST
EXCLUDE	Stat is missing
FLDSCRFLAG	Stat is missing
GENAUTO	Stat ="Yes"	Yes
GENSKM	Stat ="PROD"	PROD
MSTTOT	input(Stat,8.) >0	29144

lookup dataset:

RefCheck	StatCheck
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
ACCOMMFLAG	Stat is missing
ADMINISTRATION	Stat ="&Admin."
DATERUN	Stat ="&today."
DELMODE	Stat ="MST"
EXCLUDE	Stat is missing
FLDSCRFLAG	Stat is 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" "READYTOARC
SESRSTRT	Stat in("0", "3", "4", "5")
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")

[andreas_lds]

As long as you don't have valid expressions in "StatCheck" you can't use resolve, e.g. "Stat is missing" is not a valid expression. The function resolve expects a macro-expression. A missing char variables is blank, so end up comparing blank with blank which is a syntax error - in the macro language strings aren't quoted, so the expression would look like

%eval( = )

I recommend following the way suggested by @Tom 

[DanielLangley]

Can you please provide both the lookups and the rules in the form of datasteps so we have an idea about how the variables actually look. For example it looks like key4 should be being treated as a number but is currently part of a character variable.

[andreas_lds]

Similar things have been asked before, see https://communities.sas.com/t5/SAS-Programming/Evaluate-Expression-Stored-in-a-Dataset/td-p/107957 for one example.

The persisting problem is resolving "check" or rather creating a resolvable expression, because with the given lookup-dataset analysing "check" is required to create valid expressions.

[SK4]

Thank you everyone for a quick response.  I will try out these suggestions and post back with the results.

[Tom]

Use the metadata to generate CODE. Then run the generated code.

Personally I prefer to use a data step with PUT statement(s) to generate the code to file.  That way the code file can be examined and make sure the generation logic is correct.

For this to work the values in your metadata will need to be modified so that they can be used to generate valid SAS code.

For example how could you convert '<3 or missing' into a valid SAS expression for the variable KEY4?

[SK4]

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;