Macro for QA

[VD17]

I am checking if in my dataset the logic for assigning a particular stage is correct or not by reading in the reference sheet. In the reference sheet for certain variable the value is any( which means any value is valid if non missing). Now when I compare the two in my macro, SAS thinks any is a value and searches for the string ‘any’. How do I make SAS understand the when ‘any’ in a variable, then include all non missing values for that field as valid?

[Kurt_Bremser]

Please post your code.

[VD17]

Excel sheet snap:
Schema t N m stage
00050 T0 N1 m0 1
00050 T0 N2 M0 2
00050 T0 Any N M1 3


SAS macro:
Data _null_;
Set one;
If (schema_id in (&schemaid.) and clin_t in (‘&t’) and clin_n in (‘&n’) and clin_m in (‘&m’) and clin_stage in (‘&stage’)
then do;
Flag=1;
End;
Run;

[Tom]

What does EXCEL have to do with this. Are you saying you have a SAS dataset (perhaps created by converting a sheet in an XLSX file?) that have data like:

data conditions;
   length schema $32 clin_t $8 clin_n $8 clin_m $8 clin_stage $8 ;
   input Schema--clin_stage ;
cards;  
00050 T0 N1  m0 1
00050 T0 N2  M0 2
00050 T0 Any M1 3
;

And what does SAS macro language have to do with this?

Are you saying you want to use the data in the CONDITIONS dataset to generate code?

It is much easier to generate code from data using a data step than the macro language.

What code do you want to generate?

Perhaps something like this?

filename code temp;
data _null_;
  set conditions ;
  file code ;
  if _n_ > 1 then put 'else ' @ ;
  put 'if ' schema = :$quote. @;
  if upcase(clin_t) ne 'ANY' then put 'and ' clin_t =:$quote. @;
  if upcase(clin_n) ne 'ANY' then put 'and ' clin_n =:$quote. @;
  if upcase(clin_m) ne 'ANY' then put 'and ' clin_m =:$quote. @;
  put 'and ' clin_stage =:$quote. 'then flag=1;' ;
run;

Result

if schema="00050" and clin_t="T0" and clin_n="N1" and clin_m="m0" and clin_stage="1" then flag=1;
else if schema="00050" and clin_t="T0" and clin_n="N2" and clin_m="M0" and clin_stage="2" then flag=1;
else if schema="00050" and clin_t="T0" and clin_m="M1" and clin_stage="3" then flag=1;

Which you could then use in a data step that reads in the actual data.

data want;
  set have;
%include code / source2;
run;

[VD17]

There are two files in play here.
One is the reference document which is dynamic and will change with time. This document basically says is t is T0, N is N1 and M is M0 then stage should be 1 for schema ID 00040 and so on. So, it has atleast 1500 rows.
Second is the dataset (SAS dataset) which I want to QA is the stage is correct according to the reference table.
To do so, I read in the excel in SAS to compare with file two (SAS dataset).
For this I am using a macro (I am open to other methods), so that if the condition changes then I just have to update the excel and the program remains the same.
The excel can have AnyT, AnyN, AnyM for certain conditions. When I compare with the SAS file, SAS looks for AnyT in the dataset instead of all the valid non missing values of T and returns an error. This is what I want to fix

[Reeza]

Please provide small examples of each file and the program used to convert those conditions to SAS code.

You'll need to add macro logic to handle the Any values. Seems like you're manually building a data validation system (there are tools that do this) but if you search on lexjansen.com for data validation you should find examples as well.

[VD17]

%macro tnmsgcp;

DATA _NULL_;
IF 0 THEN SET Master_SG_cp NOBS=X;
CALL SYMPUT('RECCOUNT',X);
STOP;
RUN;

%DO i=1 %TO &reccount;

data _null_; set Master_SG_CP (obs=&i.);
call symput("SchemaId",schemaId);
call symput("t_value",t_value);
call symput("n_value",n_value);
call symput("m_value",m_value);
call symput("stage_group",stage_group);
run;

%if &t_value. ne "Any" and &n_value. ne "Any" and &m_value. ne "Any"
%then %do;
data one;

set STEP1B_TNMedits ;

if (schema_id eq (&SchemaId.) and clin_t eq ("&t_value") and clin_n eq ("&n_value")
and clin_m in ("&m_value")and clin_stage NOT in ("&stage_group") )
then do;
tnm_edit2000=1;
end;
run;
%end;
%end;
%mend tnmsgcp;

%tnmsgcp;

I am trying to use this logic now, and its gives me an error 

ERROR: Required operator not found in expression: &t_value. ne "Any" and &n_value. ne "Any" and &m_value. ne "Any"

ERROR: The macro TNMSGCP will stop executing.

Not sure what am I doing wrong. 

[Reeza]

Post your full log and example data would really help.

[Patrick]

@VD17 

Besides of the syntax error this macro logic attempts for  every single row in table Master_SG_cp to execute the data step creating table One. 

For each row there will be a full pass through source table STEP1B_TNMedits and each iteration will replace target table One. The resulting table One will be from the very last iteration. ....looks to me like you have to rethink the design of your macro/program logic.

[Tom]

First you need to know what SAS code you want to generate.

It looks like you are trying to generate code like:

data one;
  set STEP1B_TNMedits ;
  if (schema_id eq (&SchemaId.)
     and clin_t eq ("&t_value")
     and clin_n eq ("&n_value")
     and clin_m in ("&m_value")
     and clin_stage NOT in ("&stage_group") 
     ) then do;
    tnm_edit2000=1;
  end;
run;

Which raises a number of questions.

What is the source of the STEP1B_TNMedits dataset? Does it have the variables you mention in the IF statement?  Are they defined as the data types implied by how they are used in IF statement?  Is SCHEMA_ID numeric? If so then why did the values in your first post include leading zeros?  That would imply that SCHEMA_ID is a character string that consists of digits.  Are all of the other variables character strings?

Why do you use EQ operator for some of the variables and IN operator for the others?  Are you expecting those others to have a list of values in the Master_SG_CP source dataset?  If so you are not generating code that could handle that.

Why are recreating the same output dataset ONE over and over again? At the end of the %DO loop only the result from the last version of those many data steps will exist.

You also don't seem to be accounting for many things that look to me should be parameters to such a macro. 

• The name of the dataset with the parameters you want to use to create your QA findings.  
• The name of the dataset with the actual data that is to be checked.
• The name of the dataset (or datasets) to be generated with the results of the QA checks.

[Patrick]

I had lately to deal with a very similar use case. Below what worked for me.

data have;
  input (schema_id clin_t clin_n clin_m clin_stage) ($);
  datalines;
00050 T0 N1 m0 1
00050 T0 N2 M0 2
00050 T0 XX N M1 3
00050 T0 N9 M0 2
;

data want;
  if _n_=1 then
    do;
      if 0 then set have lookup;
      dcl hash h1(dataset:'lookup', hashexp:5);
      h1.defineKey('Schema', 't', 'N', 'm', 'stage');
      h1.defineData('schema');
      h1.defineDone();
      drop Schema t N m stage;
    end;
  call missing(of _all_);

  set have;
 
  if cmiss(schema_id, clin_t, clin_n, clin_m, clin_stage) ne 0 then; /* some exception handling */

  flag= h1.check(key:schema_id, key:clin_t, key:clin_n, key:clin_m, key:clin_stage) = 0;
  if flag ne 1 then flag= h1.check(key:schema_id, key:clin_t, key:'ANY', key:clin_m, key:clin_stage) = 0;

  /** alternative syntax **/
  /**
  flag= 
    h1.check(key:schema_id, key:clin_t, key:clin_n, key:clin_m, key:clin_stage) = 0
    or
    h1.check(key:schema_id, key:clin_t, key:'ANY', key:clin_m, key:clin_stage) = 0
    ;
  **/
run;

[Kurt_Bremser]

Your curly single quotes will cause a syntax error. And single quotes prevent the resolution of macro triggers anyway.

Further, you don't need the IN operator when you compare with single values only.

This is how you make code conditional:

data want;
set one;
flag =
  schema_id in (&schemaid.)
  and clin_t eq "&t"
  %if %upcase(&n) ne ANY
  %then %do;
  and clin_n eq "&n"
  %end;
  and clin_m eq "&m"
  and clin_stage eq "&stage"
;
run;

flag will either be 0 (false) or 1 (true).

[VD17]

I am trying this:

%macro tnmsgcp;

DATA _NULL_;
IF 0 THEN SET Master_SG_cp NOBS=X;
CALL SYMPUT('RECCOUNT',X);
STOP;
RUN;

%DO i=1 %TO &reccount;

data _null_; set Master_SG_CP (obs=&i.);
call symput("SchemaId",schemaId);
call symput("t_value",t_value);
call symput("n_value",n_value);
call symput("m_value",m_value);
call symput("stage_group",stage_group);

run;

data Invalid_Clinical_SG_&i Invalid_Path_SG_&i Invalid_PT_SG_&i;
set STEP1B_TNMedits ;
if ("&t_value") ne "Any T" then ("&t_value") in "%T%"; else ("&t_value") in ("&t_value");
if ("&n_value") ne "Any N" then ("&n_value") in "%N%"; else ("&n_value") in ("&n_value");
if ("&m_value") ne "Any M" then ("&m_value") in "%M%"; else ("&m_value") in ("&m_value");

if (schema_id in (&SchemaId.) and clin_t in ("&t_value") and clin_n in ("&n_value")
and clin_m in ("&m_value")and clin_stage NOT in ("&stage_group") )
then do;
tnm_edit2000=1;
output Invalid_Clinical_SG_&i;
end;
if (schema_id in (&SchemaId.) and path_t in ("&t_value") and path_n in ("&n_value") and
path_m in ("&m_value") and path_stage NOT in ("&stage_group") )
then do;
tnm_edit2000=1;
output Invalid_Path_SG_&i;
end;
if (schema_id in (&SchemaId.) and post_t in ("&t_value") and post_n in ("&n_value") and
post_m in ("&m_value") and post_stage NOT in ("&stage_group") )
then do;
tnm_edit2000=1;
output Invalid_PT_SG_&i;
end;
run;
%end;

%mend tnmsgcp;

%tnmsgcp;

Still it gives me error.

I am stuck. What am I doing wrong?

[Tom]

Before trying to use macro code to generate SAS code make sure you have working SAS code.

No matter what values the macro variables have this is not valid SAS syntax.

if ("&t_value") ne "Any T" then ("&t_value") in "%T%"; 
else ("&t_value") in ("&t_value");

What goes after the THEN or the ELSE has to be a valid SAS statement.  Not some boolean expression.

Your boolean expression also don't make any sense.  Why use IN to compare a single value to another single value? Use simple equality operator.

Did you mean to call a macro named T?  Did you even create such a macro?

[Reeza]

You need to add a conditional macro statement. If you have any it basically means you don't need to check the field except for missing. Honestly not sure how this requirement would overlap with your current data step shown.