subtr in a macro keeps resolving a variable name instead of values of the variable

[lichee]

Hi all,

I wanted use a macro to generate a new variable for multiple data sets with the same data format, but cannot make substr function work in the macro.  My original data sets are report1-report3, the data sets to get are report1_ck, report2_ck, and report3_ck. . The last field flag_report_error is the target variable to generate.

data report1;
infile datalines truncover dsd;
input measure_num Current $ Expect_Report_Qtr $ var1;
datalines;
1,2021Q1,Q1,17689
2,2021Q1,Q1,
3,2021Q1,,17036
4,2021Q1,Q2,
5,2021Q1,Q2,

;
run;
data report2;
infile datalines truncover dsd;
input measure_num Current $ Expect_Report_Qtr $ var1;
datalines;
1,2021Q2,Q1,
2,2021Q2,Q1,
3,2021Q2,,11791
4,2021Q2,Q2,12290
5,2021Q2,Q2,11161
;
run;
data report3;
infile datalines truncover dsd;
input measure_num Current $ Expect_Report_Qtr $ var1;
datalines;
1,2021Q3,Q1,
2,2021Q3,Q1,
3,2021Q3,,11207
4,2021Q3,Q2,
5,2021Q3,Q2,
;
run;

data report1_ck;
infile datalines truncover dsd;
input measure_num Current $ Expect_Report_Qtr $ var1 flag_report_error;
datalines;
1,2021Q1,Q1,17689,0
2,2021Q1,Q1,,1
3,2021Q1,,17036,0
4,2021Q1,Q2,,0
5,2021Q1,Q2,,0
;
run;
data report2_ck;
infile datalines truncover dsd;
input measure_num Current $ Expect_Report_Qtr $ var1 flag_report_error;
datalines;
1,2021Q2,Q1,,0
2,2021Q2,Q1,,0
3,2021Q2,,11791,0
4,2021Q2,Q2,12290,0
5,2021Q2,Q2,11161,0
;
run;
data report3_ck;
infile datalines truncover dsd;
input measure_num Current $ Expect_Report_Qtr $ var1 flag_report_error;
datalines;
1,2021Q3,Q1,,0
2,2021Q3,Q1,,0
3,2021Q3,,11207,0
4,2021Q3,Q2,,0
5,2021Q3,Q2,,0
;
run;

The code I use is as follows:

%macro check_field();
%do i=1 %to 3;
data report&i._ck;
set report&i.;
if %sysfunc(substr(Current,5))='Q1' and Expect_Report_Qtr='Q1' and var1=. then flag_report_ERROR=1;
/*When the field "current" contains "Q1" and Expect_Report_Qtr has the value of Q1, var1 needs to be non-missing. */
if %sysfunc(substr(Current,5))='Q2' and Expect_Report_Qtr='Q2' and var1=. then flag_report_ERROR=1;
/*When the field "current" contains "Q2" and Expect_Report_Qtr has the value of Q2, var1 needs to be non-missing. */
run;
proc freq data=report&i._ck;
table flag_report_ERROR;
run;
%end;
%mend;

%check_field;

Can anyone help correct my code?

Thank you!

[PaigeMiller · Accepted solution]

Macro functions, such as %SYSFUNC, cannot access the values of data step variables.

Use this instead

if substr(Current,5)='Q1' ...

of course, there may be other errors in your code that I have not looked for.

[ballardw]

You may find that creating date variables instead of character values like 2023Q1 is easier in the long run. If it was an actual date you could test for the quarter using the QUARTER function.

You might also want to look at the result using

flag_report_ERROR= (substr(Current,5))=Expect_Report_Qtr) and var1=.;

Instead of , I presume, 4 IF statements similar to yours. The above will assign 0 when not true which will make it easier to do things like calculate a percentage of records with the error as the MEAN of a 1/0 coded variable is percent true and SUM is count of true.

[Patrick]

Do you really need a macro? Looking at your code couldn't you do something as simple as below instead?

data report_check;
  length _source_table $41 source_table $32 flag_report_ERROR 3;
  set report1 report2 report3 indsname=_source_table;
  source_table=scan(_source_table,-1,'.');

  if missing(var1) and substr(Current,5)=Expect_Report_Qtr then flag_report_ERROR=1;
  else flag_report_ERROR=0;
run;

proc freq data=report_check;
  table source_table*flag_report_ERROR /norow nocol nopercent;
run;

[Kurt_Bremser]

The macro processor is a preprocessor which assists in creating code for the SAS interpreter. It does its work before the DATA step is even compiled, so it can hever have access to the variables in the DATA step, only the text you feed it.