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;
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
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
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.