Re: Macro troubleshooting - using include, eval, and put with a macro

MHines · Posted 05-28-2021 01:35 PM

Hello,

I am currently trying to troubleshoot a macro followed by a datastep. The purpose of the program is to conduct a data quality check by identifying observations that take invalid values, which are determined by a separate variable format program that is called on using %include. The program is not designed to catch errors in the data itself, but is intended to catch errors in the variable formats -- for example, if we coded a variable as 1="yes" and 2="no" but forgot to set a value label for 3=. The code's original author intended to structure it so that the macro was applied to one variable "section" at a time, looping through all of the variable "sections" so that they could be checked separately. Here is the current version of the program:

%macro checkvalid(sectionname, sectionformat);

format val_var $50. list flist $9000.;
list = "&&&sectionname.";
flist = "&&&sectionformat.";
counter= countw("&&&sectionname.");
%let counterN = $eval(counter);
put counter = "macro var = &counterN.";
put flist = ;

%do i = 1 %to &counterN;
%let var = %scan(list,&i,' ');
%let fmt = %scan(flist,&i,' ');
put "var= " &var.;
put "fmt= " &fmt.;
val_var = put(&var., &fmt.);
put val_var=;
%end;
%mend;

*** apply formats that have 'Invalid' for all non valid/non missing values ***;
data want_obs_invalid;
set have;

%include "filepath\variable_formats_code.sas";

%let sectionname = &sectcmpl. ;
%let sectionformat = &sectfmt. ;
format val_var $50. list flist $9000.;
list = "&&&sectionname.";
flist = "&&&sectionformat.";
%let counter= countw("&&&sectionname.");
%let counterN = $eval(counter);
put counter = "macro var = &counterN.";
put flist = ;
/* Loop through the variables and check for invalid values */
%checkvalid(sectionname, sectionformat);
run;

************;

The errors I am getting are "ERROR: Required operator not found in expression: &counterN
ERROR: The %TO value of the %DO I loop is invalid." I would appreciate any suggestions on how to resolve the issue (as well as any suggestions for a more efficient way of going about this!). Thank you!

Reeza · Posted 05-28-2021 01:39 PM

Can you add the following options before your macro and then show the full log?

options mprint symbolgen;

That will provide more information making it easier to debug this.

Spoiler

@MHines wrote:

Hello,

I am currently trying to troubleshoot a macro followed by a datastep. The purpose of the program is to conduct a data quality check by identifying observations that take invalid values, which are determined by a separate variable format program that is called on using %include. The program is not designed to catch errors in the data itself, but is intended to catch errors in the variable formats -- for example, if we coded a variable as 1="yes" and 2="no" but forgot to set a value label for 3=. The code's original author intended to structure it so that the macro was applied to one variable "section" at a time, looping through all of the variable "sections" so that they could be checked separately. Here is the current version of the program:

%macro checkvalid(sectionname, sectionformat);

format val_var $50. list flist $9000.;
list = "&&&sectionname.";
flist = "&&&sectionformat.";
counter= countw("&&&sectionname.");
%let counterN = $eval(counter);
put counter = "macro var = &counterN.";
put flist = ;

%do i = 1 %to &counterN;
%let var = %scan(list,&i,' ');
%let fmt = %scan(flist,&i,' ');
put "var= " &var.;
put "fmt= " &fmt.;
val_var = put(&var., &fmt.);
put val_var=;
%end;
%mend;

*** apply formats that have 'Invalid' for all non valid/non missing values ***;
data want_obs_invalid;
set have;

%include "filepath\variable_formats_code.sas";

%let sectionname = &sectcmpl. ;
%let sectionformat = &sectfmt. ;
format val_var $50. list flist $9000.;
list = "&&&sectionname.";
flist = "&&&sectionformat.";
%let counter= countw("&&&sectionname.");
%let counterN = $eval(counter);
put counter = "macro var = &counterN.";
put flist = ;
/* Loop through the variables and check for invalid values */
%checkvalid(sectionname, sectionformat);
run;

************;

The errors I am getting are "ERROR: Required operator not found in expression: &counterN
ERROR: The %TO value of the %DO I loop is invalid." I would appreciate any suggestions on how to resolve the issue (as well as any suggestions for a more efficient way of going about this!). Thank you!

wrote: Hello, I am currently trying to troubleshoot a macro followed by a datastep. The purpose of the program is to conduct a data quality check by identifying observations that take invalid values, which are determined by a separate variable format program that is called on using %include. The program is not designed to catch errors in the data itself, but is intended to catch errors in the variable formats -- for example, if we coded a variable as 1="yes" and 2="no" but forgot to set a value label for 3=. The code's original author intended to structure it so that the macro was applied to one variable "section" at a time, looping through all of the variable "sections" so that they could be checked separately. Here is the current version of the program: %macro checkvalid(sectionname, sectionformat); format val_var $50. list flist $9000.;list = "&&&sectionname.";flist = "&&&sectionformat.";counter= countw("&&&sectionname.");%let counterN = $eval(counter);put counter = "macro var = &counterN.";put flist = ;%do i = 1 %to &counterN;%let var = %scan(list,&i,' ');%let fmt = %scan(flist,&i,' ');put "var= " &var.;put "fmt= " &fmt.;val_var = put(&var., &fmt.);put val_var=;%end;%mend; *** apply formats that have 'Invalid' for all non valid/non missing values ***;data want_obs_invalid;set have; %include "filepath\variable_formats_code.sas"; %let sectionname = &sectcmpl. ;%let sectionformat = &sectfmt. ;format val_var $50. list flist $9000.;list = "&&&sectionname.";flist = "&&&sectionformat.";%let counter= countw("&&&sectionname.");%let counterN = $eval(counter);put counter = "macro var = &counterN.";put flist = ;/* Loop through the variables and check for invalid values */%checkvalid(sectionname, sectionformat);run; ************; The errors I am getting are "ERROR: Required operator not found in expression: &counterNERROR: The %TO value of the %DO I loop is invalid." I would appreciate any suggestions on how to resolve the issue (as well as any suggestions for a more efficient way of going about this!). Thank you!

MHines · Posted 05-28-2021 01:46 PM

Thank you for the suggestion -- I have added the full log as a reply to the original post.

MHines · Posted 05-28-2021 01:43 PM

Here is the full log:


14672  options mprint symbolgen;
14673  %macro chkvalid(snm, sfmt);
14674
14675      format val_var $50. list flist $9000.;
14676      list = "&&&snm.";
14677      flist = "&&&sfmt.";
14678      counter= countw("&&&snm.");
14679      %let counterN = $eval(counter);
14680      put counter =  "macro var = &counterN.";
14681      put flist = ;
14682
14683      %do i = 1 %to &counterN;
14684          %let var = %scan(list,&i,' ');
14685          %let fmt = %scan(flist,&i,' ');
14686          put "var= " &var.;
14687          put "fmt= " &fmt.;
14688          val_var = put(&var., &fmt.);
14689          put val_var=;
14690      %end;
14691
14692  %mend;
14693  *** apply formats that have 'Invalid' for all non valid/non missing values ***;
14694  data redcap_probs /* (keep=studyid section variable_name val_var)*/;
SYMBOLGEN:  Macro variable INREDCAP resolves to inlib.GEIPR_data_20210505
14695      set &inredcap.(obs=10 where=(fully_enrolled=1));
14696
14697      %include "S:\Projects\Inpatient_MOA\PROGRAMS\GEIPR\DQ_Checks_Final\11_DQ_formatcode.sas";
15322
15323      %let snm = &sectcmpl. ;
SYMBOLGEN:  Macro variable SECTCMPL resolves to prescreening_form_complete                         control_matching_ver_complete
                    recruitment_screening_complete                         consent_form_complete
            illness_interview_complete                         case_matching_criteria_complete                         enrollment_residual_part1
                                    enrollment_residual_part2                         enrollment_interview_complete
            casecontrol_match_complete                         first_followup_schedule_complete
            enrollment_incentive_complete                         first_followup_phone_complete
            first_followup_blood_complete                         first_followup_inter_complete
            first_followup_incen_complete                         readmit_1_blood_complete                         readmit_2_blood_complete
                               readmit_3_blood_complete                         vaccine_verification_complete
            death_form_complete                         withdrawal_form_complete
15324      %let sfmt = &sectfmt. ;
SYMBOLGEN:  Macro variable SECTFMT resolves to SECTION_COMPLETE.                         SECTION_COMPLETE.
            SECTION_COMPLETE.                         SECTION_COMPLETE.                         SECTION_COMPLETE.
            SECTION_COMPLETE.                         SECTION_COMPLETE.                         SECTION_COMPLETE.
            SECTION_COMPLETE.                         SECTION_COMPLETE.                         SECTION_COMPLETE.
            SECTION_COMPLETE.                         SECTION_COMPLETE.                         SECTION_COMPLETE.
            SECTION_COMPLETE.                         SECTION_COMPLETE.                         SECTION_COMPLETE.
            SECTION_COMPLETE.                         SECTION_COMPLETE.                         SECTION_COMPLETE.
            SECTION_COMPLETE.                         SECTION_COMPLETE.
15325      format val_var $50. list flist $9000.;
15326      list = "&&&snm.";
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable SNM resolves to prescreening_form_complete                         control_matching_ver_complete
               recruitment_screening_complete                         consent_form_complete                         illness_interview_complete
                                  case_matching_criteria_complete                         enrollment_residual_part1
            enrollment_residual_part2                         enrollment_interview_complete                         casecontrol_match_complete
                                  first_followup_schedule_complete                         enrollment_incentive_complete
            first_followup_phone_complete                         first_followup_blood_complete
            first_followup_inter_complete                         first_followup_incen_complete                         readmit_1_blood_complete
                                    readmit_2_blood_complete                         readmit_3_blood_complete
            vaccine_verification_complete                         death_form_complete                         withdrawal_form_complete
WARNING: Apparent symbolic reference PRESCREENING_FORM_COMPLETE not resolved.
15327      flist = "&&&sfmt.";
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable SFMT resolves to SECTION_COMPLETE.                         SECTION_COMPLETE.
            SECTION_COMPLETE.                         SECTION_COMPLETE.                         SECTION_COMPLETE.
            SECTION_COMPLETE.                         SECTION_COMPLETE.                         SECTION_COMPLETE.
            SECTION_COMPLETE.                         SECTION_COMPLETE.                         SECTION_COMPLETE.
            SECTION_COMPLETE.                         SECTION_COMPLETE.                         SECTION_COMPLETE.
            SECTION_COMPLETE.                         SECTION_COMPLETE.                         SECTION_COMPLETE.
            SECTION_COMPLETE.                         SECTION_COMPLETE.                         SECTION_COMPLETE.
            SECTION_COMPLETE.                         SECTION_COMPLETE.
WARNING: Apparent symbolic reference SECTION_COMPLETE not resolved.
15328      %let counter= countw("&&&snm.");
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable SNM resolves to prescreening_form_complete                         control_matching_ver_complete
               recruitment_screening_complete                         consent_form_complete                         illness_interview_complete
                                  case_matching_criteria_complete                         enrollment_residual_part1
            enrollment_residual_part2                         enrollment_interview_complete                         casecontrol_match_complete
                                  first_followup_schedule_complete                         enrollment_incentive_complete
            first_followup_phone_complete                         first_followup_blood_complete
            first_followup_inter_complete                         first_followup_incen_complete                         readmit_1_blood_complete
                                    readmit_2_blood_complete                         readmit_3_blood_complete
            vaccine_verification_complete                         death_form_complete                         withdrawal_form_complete
WARNING: Apparent symbolic reference PRESCREENING_FORM_COMPLETE not resolved.
15329      %let counterN = $eval(counter); /*https://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/viewer.htm#a000208971.htm*/
15330      put counter =  "macro var = &counterN.";
SYMBOLGEN:  Macro variable COUNTERN resolves to $eval(counter)
15331      put flist = ;
15332  /* Loop through the variables and check for invalid values */
15333      %chkvalid(sectcmpl, sectfmt);
MPRINT(CHKVALID):   format val_var $50. list flist $9000.;
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable SNM resolves to sectcmpl
SYMBOLGEN:  Macro variable SECTCMPL resolves to prescreening_form_complete                         control_matching_ver_complete
                    recruitment_screening_complete                         consent_form_complete
            illness_interview_complete                         case_matching_criteria_complete                         enrollment_residual_part1
                                    enrollment_residual_part2                         enrollment_interview_complete
            casecontrol_match_complete                         first_followup_schedule_complete
            enrollment_incentive_complete                         first_followup_phone_complete
            first_followup_blood_complete                         first_followup_inter_complete
            first_followup_incen_complete                         readmit_1_blood_complete                         readmit_2_blood_complete
                               readmit_3_blood_complete                         vaccine_verification_complete
            death_form_complete                         withdrawal_form_complete
MPRINT(CHKVALID):   list = "prescreening_form_complete                         control_matching_ver_complete
recruitment_screening_complete                         consent_form_complete                         illness_interview_complete
       case_matching_criteria_complete                         enrollment_residual_part1                         enrollment_residual_part2
                  enrollment_interview_complete                         casecontrol_match_complete
first_followup_schedule_complete                         enrollment_incentive_complete                         first_followup_phone_complete
                    first_followup_blood_complete                         first_followup_inter_complete
first_followup_incen_complete                         readmit_1_blood_complete                         readmit_2_blood_complete
       readmit_3_blood_complete                         vaccine_verification_complete                         death_form_complete
         withdrawal_form_complete";
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable SFMT resolves to sectfmt
SYMBOLGEN:  Macro variable SECTFMT resolves to SECTION_COMPLETE.                         SECTION_COMPLETE.
            SECTION_COMPLETE.                         SECTION_COMPLETE.                         SECTION_COMPLETE.
            SECTION_COMPLETE.                         SECTION_COMPLETE.                         SECTION_COMPLETE.
            SECTION_COMPLETE.                         SECTION_COMPLETE.                         SECTION_COMPLETE.
            SECTION_COMPLETE.                         SECTION_COMPLETE.                         SECTION_COMPLETE.
            SECTION_COMPLETE.                         SECTION_COMPLETE.                         SECTION_COMPLETE.
            SECTION_COMPLETE.                         SECTION_COMPLETE.                         SECTION_COMPLETE.
            SECTION_COMPLETE.                         SECTION_COMPLETE.
MPRINT(CHKVALID):   flist = "SECTION_COMPLETE.                         SECTION_COMPLETE.                         SECTION_COMPLETE.
          SECTION_COMPLETE.                         SECTION_COMPLETE.                         SECTION_COMPLETE.
SECTION_COMPLETE.                         SECTION_COMPLETE.                         SECTION_COMPLETE.                         SECTION_COMPLETE.
                       SECTION_COMPLETE.                         SECTION_COMPLETE.                         SECTION_COMPLETE.
    SECTION_COMPLETE.                         SECTION_COMPLETE.                         SECTION_COMPLETE.
SECTION_COMPLETE.                         SECTION_COMPLETE.                         SECTION_COMPLETE.                         SECTION_COMPLETE.
                       SECTION_COMPLETE.                         SECTION_COMPLETE.";
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable SNM resolves to sectcmpl
SYMBOLGEN:  Macro variable SECTCMPL resolves to prescreening_form_complete                         control_matching_ver_complete
                    recruitment_screening_complete                         consent_form_complete
            illness_interview_complete                         case_matching_criteria_complete                         enrollment_residual_part1
                                    enrollment_residual_part2                         enrollment_interview_complete
            casecontrol_match_complete                         first_followup_schedule_complete
            enrollment_incentive_complete                         first_followup_phone_complete
            first_followup_blood_complete                         first_followup_inter_complete
            first_followup_incen_complete                         readmit_1_blood_complete                         readmit_2_blood_complete
                               readmit_3_blood_complete                         vaccine_verification_complete
            death_form_complete                         withdrawal_form_complete
MPRINT(CHKVALID):   counter= countw("prescreening_form_complete                         control_matching_ver_complete
recruitment_screening_complete                         consent_form_complete                         illness_interview_complete
       case_matching_criteria_complete                         enrollment_residual_part1                         enrollment_residual_part2
                  enrollment_interview_complete                         casecontrol_match_complete
first_followup_schedule_complete                         enrollment_incentive_complete                         first_followup_phone_complete
                    first_followup_blood_complete                         first_followup_inter_complete
first_followup_incen_complete                         readmit_1_blood_complete                         readmit_2_blood_complete
       readmit_3_blood_complete                         vaccine_verification_complete                         death_form_complete
         withdrawal_form_complete");
SYMBOLGEN:  Macro variable COUNTERN resolves to $eval(counter)
MPRINT(CHKVALID):   put counter = "macro var = $eval(counter)";
MPRINT(CHKVALID):   put flist = ;
SYMBOLGEN:  Macro variable COUNTERN resolves to $eval(counter)
ERROR: Required operator not found in expression: &counterN
ERROR: The %TO value of the %DO I loop is invalid.
ERROR: The macro CHKVALID will stop executing.
15334  run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.REDCAP_PROBS may be incomplete.  When this step was stopped there were 0 observations and 994 variables.
WARNING: Data set WORK.REDCAP_PROBS was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           6.04 seconds
      cpu time            3.78 seconds

ballardw · Posted 05-28-2021 01:59 PM

Lets start with something simple:

You have this line in your code:

%let counterN = $eval(counter);

The macro function would be %eval, not $eval.

Second, what is "counter"? The macro language does not see values of data set variables.

So even the "proper" call would yield:

671  %let counterN = %eval(counter);
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric
       operand is required. The condition was: counter

because the string "counter" does not contain anything that can resolve to a numeric value.

These are valid

%let counterN = %eval(1 + 2);
%put Countern is: &countern;

%let counterN = %eval(a=b);
%put Countern is: &countern;

The equality comparison reports if the character a is the same as the character b and SAS returns 0 for false. But that is not looking at any variable value.

MHines · Posted 05-28-2021 02:15 PM

My understanding is that "counter" is meant to identify the names of the variables within the section and counterN is meant to evaluate the values each variable takes within the section -- do you think it makes more sense to just ignore the "sections" and just look at all variables at once? Would an array with a do loop make more sense? I re-ran the code using %eval instead of $eval and, as you indicated, got the following error "ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: counter
ERROR: The macro CHKVALID will stop executing."

ballardw · Posted 05-28-2021 03:23 PM

@MHines wrote:

My understanding is that "counter" is meant to identify the names of the variables within the section and counterN is meant to evaluate the values each variable takes within the section -- do you think it makes more sense to just ignore the "sections" and just look at all variables at once? Would an array with a do loop make more sense? I re-ran the code using %eval instead of $eval and, as you indicated, got the following error "ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: counter
ERROR: The macro CHKVALID will stop executing."

The macro language is preprocessed prior to the data step actually executing so it can build the code elements that a procedure or data step might use. PREPROCESS. So the macro functions and statements do not ever see the value of any data step variable. You created a data step variable and then attempt to use %eval on that variable.

This looks like what I think "checkvalid" is partially supposed to do at least as far a pullling a list of values apart and aligning two lists.

%let list1 = a b c d;
%let list2 = 1. 2. 3. 4.;

%macro listloop( v1, v2);
   %do i=1 %to %sysfunc(countw(&v1.));
      %let var = %scan(&v1.,&i,' ');
      %let fmt = %scan(&v2.,&i,' ');
      %put i= &i. var= &var. fmt= &fmt.;
   %end;
%mend;

%listloop (&list1. , &list2.);

I don't actually see anything "validating" values, especially not any useful way.

As far as I can tell if your program "worked" you would have a log.

Looping through a data set with a pile of "put" statements doesn't sound very efficient.

I suggest providing: a small sample data set, maybe 4 or 5 variables, and enough records to show some variable with "all" the format values and a couple without (so we have something invalid). The what you might expect.

Tom · Posted 05-28-2021 02:28 PM

Your basic program structure does make sense to me.

What is in the %INCLUDE file? The way you have written your program it should only include statements that would be valid in a data step. But based on the name it looks like it might include some PROC FORMAT code. If it does then you should run the PROC FORMAT step before starting your data step.

If you add the / source2 option to the %INCLUDE statement you will see the lines of code in the LOG.

Macro statements run before the data step (they run before SAS even starts trying to compile the data step). So move your %LET statements before the DATA statement.

The macro seems to sort of just generate statements to run in a data step. So you might be able to call it multiple times in the same data step.

So perhaps your code needs to look something like:

%include "filepath\variable_formats_code.sas";
%macro checkvalid(section,format);
/* generate data step code to check if variable SECTION
   has values that are valid for format FORMAT
*/
%mend ;

data want_obs_invalid;
  set have;
 %checkvalid(section1, formatA);
 %checkvalid(section2, formatB);
run;

But you have a lot of details still to work out.

MHines · Posted 05-28-2021 02:35 PM

Hi Tom, you are correct -- my datastep does call on the macro repeatedly to loop through the sections; I just included it once here because I figured that the repeated use of the macro was not the source of the error.

The %include file is structured as:

format var_1 var_1_fmt.

format var_2 var_2_fmt.

format var_3 var_3_fmt.

For example, here are the first two:

format ps_age50 ps_age50_.;
format ps_admit10 ps_admit10_.;

The program does call on a format library prior to the macro and %include statement, as follows:

libname dqfmtlib "S:\filepath\DQFormatlib";
options orientation=landscape fmtsearch=(dqfmtlib) ps=39 ls=145 compress=yes errors=1 nofmterr
symbolgen nocenter noquotelenmax /*noreplace*/;

Tom · Posted 05-28-2021 03:23 PM

So what is the logic for telling if variable ps_age50 has valid values for the format ps_age50_. ?

Do you just want to check if the formatted value is different from the raw value?

if cats(ps_age50) ne vvalue(ps_age50) then put 'ERROR: Unformatted value ' ps_age50 =;

Or does the format definition for PS_AGE50_ format include and OTHER category that displays invalid data as some fixed text, like UNKNOWN?

if 'UNKNOWN' eq vvalue(ps_age50) then put 'ERROR: Unformatted value ' ps_age50 = best32.;

MHines · Posted 06-02-2021 10:31 AM

Hello, thank you for the question and apologies for the delayed response! The formats do include an "other" category to catch invalid values. Our goal is to identify instances where the values fall into the "other" category. See below for an example:

----------------------------------------------------------------------------                                                                     
|                   FORMAT NAME: CASECONTROL_ LENGTH: 12                   |                                                                     
|   MIN LENGTH:   1  MAX LENGTH:  40  DEFAULT LENGTH:  12  FUZZ: STD       |                                                                     
|--------------------------------------------------------------------------|                                                                     
|START           |END             |LABEL  (VER. 9.4     26MAY2021:10:25:26)|                                                                     
|----------------+----------------+----------------------------------------|                                                                     
|               .|               .|missing                                 |                                                                     
|               1|               1|Case                                    |                                                                     
|               2|               2|Control                                 |                                                                     
|**OTHER**       |**OTHER**       |Invalid Code                            |                                                                     
----------------------------------------------------------------------------

Ready to join fellow brilliant minds for the SAS Hackathon?

Classroom Training Available!