date difference between two dates

[jorquec]

Hi, 

I need to be able to pick the maximum date available in the data and look back 28 days from that day. The reason behind this is that the table doesn’t get run daily sometimes so the code needs to adjust to what data we have available. I am able to macro the date for the maximum date in the data but I can’t get the date to work for 28 days ago.

Please someone could you have a look and see what I’m doing wrong?

Many thanks in advance.

 /*Code*/

proc sql noprint;
select max(event_dt) format yymmdd10. into :date_1
from pl_uview.subscr_usage_summ_daily ;

%let sql_date_max1=%str(%')&date_1.%str(%'); 
%put &sql_date_max1.;
quit;

%put &date_1.;
%let date_f1=&date_1.;
%put &date_f1.;

data _null_;
date_1_28=intnx("day",&date_f1.,-28);
call symput('sql_date_1_28',substr(put(date_1_28,DDMMYY10.),7,4)||"-"||substr(put(date_1_28,DDMMYY10.),4,2)||"-"||substr(put(date_1_28,DDMMYY10.),1,2));
run;

%let sql_date_1_28=%str(%')&sql_date_1_28.%str(%'); *(-1M start);
%put &sql_date_1_28.;

Log File report: 

                                              The SAS System                          14:24 Wednesday, November 6, 2019

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=PNG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         FILENAME EGSR TEMP;
15         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16             STYLE=HtmlBlue
17             STYLESHEET=(URL="file:///C:/SAS94/software/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
18             NOGTITLE
19             NOGFOOTNOTE
20             GPATH=&sasworklocation
21             ENCODING=UTF8
22             options(rolap="on")
23         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
26         
27         %let sql_date_max1=%str(%')&date_1.%str(%');
28         %put &sql_date_max1.;
'2019-11-04'
29         quit;
30         
31         GOPTIONS NOACCESSIBLE;
32         %LET _CLIENTTASKLABEL=;
33         %LET _CLIENTPROCESSFLOWNAME=;
34         %LET _CLIENTPROJECTPATH=;
35         %LET _CLIENTPROJECTPATHHOST=;
36         %LET _CLIENTPROJECTNAME=;
37         %LET _SASPROGRAMFILE=;
38         %LET _SASPROGRAMFILEHOST=;
39         
40         ;*';*";*/;quit;run;
41         ODS _ALL_ CLOSE;
42         
43         
44         QUIT; RUN;

1                                                          The SAS System                          14:24 Wednesday, November 6, 2019

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=PNG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         FILENAME EGSR TEMP;
15         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16             STYLE=HtmlBlue
17             STYLESHEET=(URL="file:///C:/SAS94/software/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
18             NOGTITLE
19             NOGFOOTNOTE
20             GPATH=&sasworklocation
21             ENCODING=UTF8
22             options(rolap="on")
23         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
26         
27         %put &date_1.;
2019-11-04
28         %let date_f1=&date_1.;
29         %put &date_f1.;
2019-11-04
30         
31         data _null_;
32         date_1_28=intnx("day",&date_f1.,-28);
33         call
33       ! symput('sql_date_1_28',substr(put(date_1_28,DDMMYY10.),7,4)||"-"||substr(put(date_1_28,DDMMYY10.),4,2)||"-"||substr(put(d
33       ! ate_1_28,DDMMYY10.),1,2));
34         run;

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              336.90k
      OS Memory           21152.00k
      Timestamp           06/11/2019 02:38:03 PM
      Step Count                        3  Switch Count  0
      Page Faults                       0
      Page Reclaims                     133
      Page Swaps                        0
      Voluntary Context Switches        0
      Involuntary Context Switches      1
      Block Input Operations            0
      Block Output Operations           0
      

35         
2                                                          The SAS System                          14:24 Wednesday, November 6, 2019

36         %let sql_date_1_28=%str(%')&sql_date_1_28.%str(%'); *(-1M start);
37         %put &sql_date_1_28.;
'1965-05-30'
38         
39         
40         GOPTIONS NOACCESSIBLE;
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         

[Kurt_Bremser]

When you do this:

date_1_28=intnx("day",&date_f1.,-28);

and the macro variable is resolved, you get this code:

date_1_28=intnx("day",2019-11-04,-28);

so SAS is working from the date value 2019 minus 11 minus 4, which results in a date of 2004 days after 1960-01-01 which is the date in 1965.

[Kurt_Bremser · Accepted solution]

If you need the date value for comparison in SAS, do this:

proc sql noprint;
select max(event_dt) - 28 into :date_1
from pl_uview.subscr_usage_summ_daily;
quit;

and use this raw value. If you need to format it for some foreign software (e.g. a DBMS), add a format and quotes:

proc sql noprint;
select cats("'",put(max(event_dt) - 28,yymmddd10.),"'") into :date_1
from pl_uview.subscr_usage_summ_daily;
quit;

[jorquec]

Many thanks for your help, lovely this is perfect.