BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Sandeep77
Lapis Lazuli | Level 10

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 ACCEPTED SOLUTION

Accepted Solutions
yabwon
Onyx | Level 15

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

 

 

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



View solution in original post

1 REPLY 1
yabwon
Onyx | Level 15

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

 

 

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 1 reply
  • 1677 views
  • 1 like
  • 2 in conversation