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 = "&&§ionname.";
flist = "&&§ionformat.";
counter= countw("&&§ionname.");
%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 = §cmpl. ;
%let sectionformat = §fmt. ;
format val_var $50. list flist $9000.;
list = "&&§ionname.";
flist = "&&§ionformat.";
%let counter= countw("&&§ionname.");
%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!
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.
@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 = "&&§ionname.";
flist = "&&§ionformat.";
counter= countw("&&§ionname.");
%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 = §cmpl. ;
%let sectionformat = §fmt. ;
format val_var $50. list flist $9000.;
list = "&&§ionname.";
flist = "&&§ionformat.";
%let counter= countw("&&§ionname.");
%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!
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 = §cmpl. ; 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 = §fmt. ; 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
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.
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."
@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.
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.
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*/;
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.;
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 | ---------------------------------------------------------------------------- |
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: