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;
%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
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.
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.
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;
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).
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?
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?
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.