topic Re: Macro for QA in SAS Programming

Macro for QA

VD17 — Mon, 20 Dec 2021 22:32:41 GMT

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?

Re: Macro for QA

Kurt_Bremser — Mon, 20 Dec 2021 22:37:40 GMT

Please post your code.

Re: Macro for QA

VD17 — Mon, 20 Dec 2021 23:21:55 GMT

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;

Re: Macro for QA

Reeza — Mon, 20 Dec 2021 23:39:02 GMT

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.

Re: Macro for QA

Tom — Tue, 21 Dec 2021 00:30:56 GMT

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;

Re: Macro for QA

Patrick — Tue, 21 Dec 2021 03:12:30 GMT

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;

Re: Macro for QA

VD17 — Tue, 21 Dec 2021 06:05:24 GMT

Thank you for the comments..
So, the reference sheet is huge with lots of schema and various conditions for each schema which are dynamic. So, I am using an excel sheet and reading it as macro so that when the value changes, I just have to update the excel.

Re: Macro for QA

VD17 — Tue, 21 Dec 2021 06:07:02 GMT

I agree.. and that what I am struggling with. I tried putting a condition
If &t = anyT the &t ne .
But it doesn’t work..

Re: Macro for QA

VD17 — Tue, 21 Dec 2021 06:09:50 GMT

I was wondering if something like * instead of any would make SAS take it as any value? I tried * and ** but doesn’t work..
I am thinking if something on that line though would read directly from excel to SAS as Any

Re: Macro for QA

Kurt_Bremser — Tue, 21 Dec 2021 09:58:15 GMT

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).

Re: Macro for QA

Patrick — Tue, 21 Dec 2021 10:35:33 GMT

@VD17 Not sure why you need a macro at all. The approach I've provided earlier will work and is easily expandable should you also have "any" for other columns.

Re: Macro for QA

VD17 — Wed, 22 Dec 2021 18:26:52 GMT

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

Re: Macro for QA

Reeza — Wed, 22 Dec 2021 18:39:39 GMT

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.

Re: Macro for QA

Tom — Wed, 22 Dec 2021 20:57:06 GMT

@VD17 wrote:
Thank you for the comments..
So, the reference sheet is huge with lots of schema and various conditions for each schema which are dynamic. So, I am using an excel sheet and reading it as macro so that when the value changes, I just have to update the excel.

It sounds like your code generation needs are extensive. It will probably be a lot easier to generate the code using SAS code instead of macro code. You will not need to move the reference data into macro variables. You can more naturally test for things like the ANY criteria you mentioned.

To get better example code provide a clearer picture of what you are doing. What are you checking for? How have decided to store the reference data to describe those checks? What do you think is the code you need to generate to make those checks?

Re: Macro for QA

VD17 — Wed, 22 Dec 2021 23:19:46 GMT

%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.

Re: Macro for QA

Reeza — Wed, 22 Dec 2021 23:28:16 GMT

Post your full log and example data would really help.

Re: Macro for QA

Patrick — Thu, 23 Dec 2021 01:47:06 GMT

@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.

Re: Macro for QA

Tom — Thu, 23 Dec 2021 01:47:40 GMT

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.

Re: Macro for QA

VD17 — Thu, 30 Dec 2021 00:22:25 GMT

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;

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?

Re: Macro for QA

Tom — Thu, 30 Dec 2021 04:28:53 GMT

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?