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
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;
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.
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.