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
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!
Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF
View now: on-demand content for SAS users
@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;
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") |
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
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.
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.
Thank you everyone for a quick response. I will try out these suggestions and post back with the results.
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?
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.