Macro is not showing the correct output

[Sandeep77]

Hi Experts,

I have tried a macro today again but for some reason, it does not give any output data. I tried the macro as suggested before and the code shows that it runs successfully but with no output data. Could you please check the issue in my code and suggest me what can help to get the output data?

Sample dataset

Data DPDTest;
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;
     %let HIST_DATE_END    = %sysfunc(intnx(month,"&HIST_DATE_START."d,-3,end),Date9.); 

Proc sql;
create table Checks as 
select Account_ID,
FINREP_CURR_SEGMENT,
FINREP_CURR_SEGMENT_REASON,
DPD,
DRAWN_EXPOSURE,
MONTH
from DPDTest
where month between "&HIST_DATE_START."d and "&HIST_DATE_END."d
and DPD > 30;
quit;

Log:
1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Finrep Retail Check';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='/team/caprep/code/user/5604829/Programs/Balloon Refinancing.egp';
6          %LET _CLIENTPROJECTPATHHOST='p24720prw800.machine.group';
7          %LET _CLIENTPROJECTNAME='Balloon Refinancing.egp';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=SVG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         %macro HTML5AccessibleGraphSupported;
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')
20             %HTML5AccessibleGraphSupported
21             ENCODING='utf-8'
22             STYLE=HTMLBlue
23             NOGTITLE
24             NOGFOOTNOTE
25             GPATH=&sasworklocation
26         ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
27         
28              %let HIST_DATE_START  = 01NOV2023;
29              %let HIST_DATE_END    = %sysfunc(intnx(month,"&HIST_DATE_START."d,-3,end),Date9.);
30         Proc sql;
31         create table Checks as
32         select Account_ID,
33         FINREP_CURR_SEGMENT,
34         FINREP_CURR_SEGMENT_REASON,
35         DPD,
36         DRAWN_EXPOSURE,
37         MONTH
38         from DPDTest
39         where month between "&HIST_DATE_START."d and "&HIST_DATE_END."d
40         and DPD > 30;
NOTE: Table WORK.CHECKS created, with 0 rows and 6 columns.

41         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.03 seconds
      user cpu time       0.01 seconds
      system cpu time     0.00 seconds
      memory              5472.96k
      OS Memory           39332.00k
      Timestamp           02/06/2024 10:21:04 AM
      Step Count                        29  Switch Count  2
      Page Faults                       0
      Page Reclaims                     76
      Page Swaps                        0
      Voluntary Context Switches        19
      Involuntary Context Switches      0
      Block Input Operations            0
2                                                          The SAS System                            09:03 Tuesday, February 6, 2024

      Block Output Operations           0
      

42         
43         %LET _CLIENTTASKLABEL=;
44         %LET _CLIENTPROCESSFLOWNAME=;
45         %LET _CLIENTPROJECTPATH=;
46         %LET _CLIENTPROJECTPATHHOST=;
47         %LET _CLIENTPROJECTNAME=;
48         %LET _SASPROGRAMFILE=;
49         %LET _SASPROGRAMFILEHOST=;
50         
51         ;*';*";*/;quit;run;
52         ODS _ALL_ CLOSE;
53         
54         
55         QUIT; RUN;

[yabwon · Accepted solution]

1) You are not using macro but macrovariables

2) If your MONTH variable contains proper date it works:

data DPDTest;
Account_ID=0;
FINREP_CURR_SEGMENT =2;
FINREP_CURR_SEGMENT_REASON=3;
DPD=666;
DRAWN_EXPOSURE=5;
do MONTH =  "15apr2023"d to '31dec2023'd by 5;
  Account_ID+1;
  output;
end;
run;

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

Proc sql;
create table Checks as
select Account_ID,
FINREP_CURR_SEGMENT,
FINREP_CURR_SEGMENT_REASON,
DPD,
DRAWN_EXPOSURE,
MONTH
from DPDTest
where month between "&HIST_DATE_START."d and "&HIST_DATE_END."d
and DPD > 30;
QUIT;

Log:

1    data DPDTest;
2    Account_ID=0;
3    FINREP_CURR_SEGMENT =2;
4    FINREP_CURR_SEGMENT_REASON=3;
5    DPD=666;
6    DRAWN_EXPOSURE=5;
7    do MONTH =  "15apr2023"d to '31dec2023'd by 5;
8      Account_ID+1;
9      output;
10   end;
11   run;

NOTE: The data set WORK.DPDTEST has 53 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


12
13   %let HIST_DATE_START  = 01NOV2023;
14   %let HIST_DATE_END    = %sysfunc(intnx(month,"&HIST_DATE_START."d,-3,end),Date9.);
15   %put &=HIST_DATE_START.;
HIST_DATE_START=01NOV2023
16   %put &=HIST_DATE_END.;
HIST_DATE_END=31AUG2023
17
18   Proc sql;
19   create table Checks as
20   select Account_ID,
21   FINREP_CURR_SEGMENT,
22   FINREP_CURR_SEGMENT_REASON,
23   DPD,
24   DRAWN_EXPOSURE,
25   MONTH
26   from DPDTest
27   where month between "&HIST_DATE_START."d and "&HIST_DATE_END."d
28   and DPD > 30;
NOTE: Table WORK.CHECKS created, with 13 rows and 6 columns.

29   QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

3) Since your month is in YYYYMM form (as a number, e.g.,202306) do it like:

%let HIST_DATE_START  = 01NOV2023;
%let MONTH_ST= %sysfunc(int("&HIST_DATE_START."d),yymmn6.);
%let MONTH_ED= %sysfunc(intnx(month,"&HIST_DATE_START."d,-3,end),yymmn6.);

%put &=MONTH_ST.;
%put &=MONTH_ED.;

Proc sql;
create table Checks as
select Account_ID,
FINREP_CURR_SEGMENT,
FINREP_CURR_SEGMENT_REASON,
DPD,
DRAWN_EXPOSURE,
MONTH
from DPDTest
where month between &MONTH_ST. and &MONTH_ED.
and DPD > 30;
QUIT;

Bart