The macro is not working.

[Sandeep77]

Hi Experts,

I am trying a code with macros and it shows successful in the submission status with no errors and few notes but does not produce any output. I am trying to find where DPD is more than 30 using a macro which looks for the last three months from November 2023. I want the output to show records with DPD more than 30 for Nov, Oct and Sep 23. Could you look at the code and let me know why it is not producing any output data?

Sample dataset:

Data DPD;
infile cards expandtabs;
input ACCOUNT_ID DPD DRAWN_EXPOSURE FINREP_CURR_SEGMENT$ FINREP_CURR_SEGMENT_REASON$ MONTH;
datalines;
1000016 0 6708.87 PERFORMING MEET 202306
1000032 34 9302.42 PERFORMING MEET 202311
1000040 0 4741.34 PERFORMING MEET 202007
1000075 86 12999.92 PERFORMING MEET 202309
1000112 0 6347.45 PERFORMING MEET 202310
;
run;

     %let HIST_DATE_START  = "01NOV2023"d;
     %let HIST_DATE_END    = %sysfunc(intnx(month,&HIST_DATE_START.,-3,end),Date9.); 
Proc sql;
create table DPD as 
select Account_ID,
FINREP_CURR_SEGMENT,
FINREP_CURR_SEGMENT_REASON,
DPD,
DRAWN_EXPOSURE,
MONTH
from FSFINREP.FINREP_LM_AF_DET
where month between &HIST_DATE_START. and &HIST_DATE_END.
and DPD > 30;
quit;

Log:
1          ;*'
             _
             49
1        ! ;*";*/;quit;run;
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release.  Inserting white space 
             between a quoted string and the succeeding identifier is recommended.

2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Macro Test'
                                            _______________________________
                                            49
3        ! ;
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release.  Inserting white space 
             between a quoted string and the succeeding identifier is recommended.

4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
NOTE: The quoted string currently being processed has become more than 262 characters long.  You might have unbalanced quotation 
      marks.
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=SVG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         %macro HTML5AccessibleGraphSupported;
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
                                                    __
                                                    49
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release.  Inserting white space 
             between a quoted string and the succeeding identifier is recommended.

15             %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16         %mend;
17         FILENAME EGHTML TEMP;
18         ODS HTML5(ID=EGHTML) FILE=EGHTML
19             OPTIONS(BITMAP_MODE='INLINE'
                                          _
                                          49
19       ! )
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release.  Inserting white space 
             between a quoted string and the succeeding identifier is recommended.

20             %HTML5AccessibleGraphSupported
21             ENCODING='utf-8'
22             STYLE=HTMLBlue
23             NOGTITLE
24             NOGFOOTNOTE
25             GPATH=&sasworklocation
26         ;
27         
28         Proc sql;
29         create table DPD as
30         select Account_ID,
31         FINREP_CURR_SEGMENT,
32         FINREP_CURR_SEGMENT_REASON,
2                                                          The SAS System                             13:11 Monday, February 5, 2024

NOTE: The quoted string currently being processed has become more than 262 characters long.  You might have unbalanced quotation 
      marks.
33         DPD,
34         DRAWN_EXPOSURE,
35         MONTH
36         from FSFINREP.FINREP_LM_AF_DET
37         where month between &HIST_DATE_START. and &HIST_DATE_END.
38         and DPD > 30;
39         quit;
40         
41         %LET _CLIENTTASKLABEL=;
42         %LET _CLIENTPROCESSFLOWNAME=;
43         %LET _CLIENTPROJECTPATH=;
44         %LET _CLIENTPROJECTPATHHOST=;
45         %LET _CLIENTPROJECTNAME=;
46         %LET _SASPROGRAMFILE=;
47         %LET _SASPROGRAMFILEHOST=;
48         
49         ;*';*";*/;quit;run;
50         ODS _ALL_ CLOSE;
51         
52         
53         QUIT; RUN;
54         

Here is my sample code.

[Tom · Accepted solution]

You seem to be getting errors BEFORE your code.

Try starting a new SAS session to clear up any unbalanced quotes or other grouping characters you have submitted and test your code again.

Why did you set HIST_DATE_START to a DATE value but set HIST_DATE_END to a character string?

Either set them both to DATE values:

%let HIST_DATE_START="01NOV2023"d;
%let HIST_DATE_END="%sysfunc(intnx(month,&HIST_DATE_START.,-3,end),Date9.)"d;

Or set them both to character strings:

%let HIST_DATE_START=01NOV2023;
%let HIST_DATE_END=%sysfunc(intnx(month,"&HIST_DATE_START."d,-3,end),Date9.);

And then convert them into date values where you need date values.

month between "&HIST_DATE_START."d and "&HIST_DATE_END."d

[Kurt_Bremser]

You had something unbalanced in previous code which caused your SAS session to become unstable. This can be unbalanced quotes, but also an unfinished macro definition (%MACRO without corresponding %MEND).

Start a new SAS session and take care while submitting code.