BookmarkSubscribeRSS Feed
SK4
Fluorite | Level 6 SK4
Fluorite | Level 6

 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

 

10 REPLIES 10
noling
SAS Employee

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

Shmuel
Garnet | Level 18

@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
Jade | Level 19
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
Fluorite | Level 6 SK4
Fluorite | Level 6

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)

RefCheckStatCheckStat
ACCOMMFLAGStat is missing 
ADMINISTRATIONStat ="&Admin."11111111A
DATERUNStat ="&today."10/22/2018
DELMODEStat ="MST"MST
EXCLUDEStat is missing 
FLDSCRFLAGStat is missing 
GENAUTOStat ="Yes"Yes
GENSKMStat ="PROD"PROD
MSTTOTinput(Stat,8.) >0 29144

 

lookup dataset:

 

RefCheckStatCheck
TOTALinput(Stat,8.) >0
MSTTOTinput(Stat,8.) >0
MSTVALinput(Stat,8.) >0
QVDOMinput(Stat,8.) >0
QVINTinput(Stat,8.) >0
WDOMinput(Stat,8.) >0
WINTinput(Stat,8.) >0
ACCOMMFLAGStat is missing
ADMINISTRATIONStat ="&Admin."
DATERUNStat ="&today."
DELMODEStat ="MST"
EXCLUDEStat is missing
FLDSCRFLAGStat is missing
GENAUTOStat ="Yes"
GENSKMStat ="PROD"
OPTIONLIBStat ="G:\\fortnt"
PROGRAMStat ="GRE"
Q1SECTTERMStat in ("0", "3", "4", "5")
Q2SECTTERMStat in ("0", "3", "4", "5")
RECTYPEStat ="DTL"
REGIDStat ="ERS"
SIRBStat ="Populated"
SIRMStat ="Populated"
SCRINDStat not in("C")
SCRPHASEStat in("RPTBL" "SCRD" "READYTOARC
SESRSTRTStat in("0", "3", "4", "5")
SESTERMStat in("0", "3", "4", "5")
SKMQNT01ANSinput(Stat,8.) >=0
SKMQNT02ANSinput(Stat,8.) >=0
SKMVBL01ANSinput(Stat,8.) >=0
SKMVBL02ANSinput(Stat,8.) >=0
SKMWRT00ARGinput(Stat,8.) >=0
SKMWRT00ISSinput(Stat,8.) >=0
SPARSEMATRIXinput(Stat,8.) >=0
TESTStat ="GEN"
V1SECTTERMStat in("0", "3", "4", "5")
V2SECTTERMStat in("0", "3", "4", "5")
andreas_lds
Jade | Level 19

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
Quartz | Level 8

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
Jade | Level 19

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
Fluorite | Level 6 SK4
Fluorite | Level 6

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

 

Tom
Super User Tom
Super User

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
Fluorite | Level 6 SK4
Fluorite | Level 6

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;

 

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 2400 views
  • 1 like
  • 6 in conversation