Hi all,
I have the below code to format the date for a monthly job im running however I can not automate this job as each month I have to change the "%Let ThisMonthStart=" with the 1st day of the month.
I want to know how I can get this autoformatted so I can automate this job, I have attached the bit of code that I use to calculate the other dates that are used in this job.
%let ThisMonthStart='01Oct2011'd; /*** The Start Date Of the Month Needs To Be Changed ***/
DATA _NULL_;
format mth lstmth $6. dt1 date9. dt2 date9. monthend1 date9.;
DT1=intnx('week.6',intnx('month',&ThisMonthStart.,0,'end'),0);
DT2=intnx('week.6',intnx('month',&ThisMonthStart.,-1,'end'),0);
MonthEnd1=intnx('month',&ThisMonthStart.,0,'end');
Mth=COMPRESS(year(dt1)*100+month(dt1));
Lstmth=COMPRESS(year(DT2)*100+month(DT2));
output;
CALL symput("rpt",mth);
CALL symput("prerpt",Lstmth);
call symput("ThisMonthEnd",monthend1);
run;
I could use some help on how I can achieve the above as it would free up a lot of my time.
Thanks in advance.
cheers
%let thismonthstart=%sysfunc(intnx(month,%sysfunc(today()),0,beginning),date9.);
%put &thismonthstart;
01NOV2011
If you want to use it as the numeric value remove ,date9. from the statement above:
18932
Or you could call it like:
"&thismonthstart"d
FriesEgg provides the answer.
Only slightly reduced, an alternative is
%let thisMonthStart = %sysfunc( intnx( month, "&sysdate"d, 0 ), date9 );
which provides the beginning of the month in which the SAS job starts
Use it like
where transaction_date GE "&thisMonthStart"d
or
where timeStamp >= "&thisMonthStart:0:0:0"dt
when you are testing against a date-time value
Peter,
That can turn out to be quite problematic as I have seen many situations where a SAS session had actually been started a number of months before a particular program was run. Scarry, maybe, but true.
Art
scary!:smileyalert:
and simply needs &sysdate replaced as demonstrated by others, with
%sysfunc( today(), date9)
peter
Not sure if I'm doing the right thing, but getting an error msg, I think this maybe due to Nov end of month being today....
1 The SAS System 09:12 Wednesday, November 30, 2011
1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL=%NRBQUOTE(Code1);
4 %LET _EGTASKLABEL=%NRBQUOTE(Code1);
5 %LET _CLIENTPROJECTNAME=%NRBQUOTE();
6 %LET _SASPROGRAMFILE=;
7
8 ODS _ALL_ CLOSE;
NOTE: Some of your options or statements may not be supported with the Activex or Java series of devices. Graph defaults for these
drivers may be different from other SAS/GRAPH device drivers. For further information, please contact Technical Support.
9 OPTIONS DEV=ACTIVEX;
10 FILENAME EGHTML TEMP;
NOTE: Writing HTML(EGHTML) Body file: EGHTML
11 ODS HTML(ID=EGHTML) FILE=EGHTML ENCODING='utf-8' STYLE=EGDefault
11 ! STYLESHEET=(URL="file:///C:/Program%20Files/SAS/Shared%20Files/BIClientStyles/EGDefault.css")
11 ! ATTRIBUTES=("CODEBASE"="http://www2.sas.com/codebase/graph/v91/sasgraph.exe") NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation
11 ! ;
12
13 %gaccessible;
14 %put &thismonthstart;
01NOV2011
15 DATA _tNULL_;
16 format mth lstmth $6. dt1 date9. dt2 date9. monthend1 date9.;
17 DT1=intnx('week.6',intnx('month',&ThisMonthStart.,0,'end'),0);
_____
72
NOTE: Line generated by the macro variable "THISMONTHSTART".
17 01NOV2011
_______
22
18 DT2=intnx('week.6',intnx('month',&ThisMonthStart.,-1,'end'),0);
_____
72
NOTE: Line generated by the macro variable "THISMONTHSTART".
18 01NOV2011
_______
22
19 MonthEnd1=intnx('month',&ThisMonthStart.,0,'end');
_____
72
NOTE: Line generated by the macro variable "THISMONTHSTART".
19 01NOV2011
_______
22
ERROR 72-185: The INTNX function call has too many arguments.
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, ><, >=, AND, EQ, GE,
GT, IN, LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, ^=, |, ||, ~=.
20 Mth=COMPRESS(year(dt1)*100+month(dt1));
21 Lstmth=COMPRESS(year(DT2)*100+month(DT2));
22 output;
23 CALL symput("rpt",mth);
24 CALL symput("prerpt",Lstmth);
25 call symput("ThisMonthEnd",monthend1);
26 run;
NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
2 The SAS System 09:12 Wednesday, November 30, 2011
17:51 18:51 19:42 20:27 21:30 25:28
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK._TNULL_ may be incomplete. When this step was stopped there were 0 observations and 6 variables.
WARNING: Data set WORK._TNULL_ was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
27
28
29 %LET _CLIENTTASKLABEL=;
30 %LET _EGTASKLABEL=;
31 %LET _CLIENTPROJECTNAME=;
32 %LET _SASPROGRAMFILE=;
33
34 ;*';*";*/;quit;run;
35 ODS _ALL_ CLOSE;
36
37
38 QUIT; RUN;
proc format;
picture fom (default=9) other='01%b%Y' (datatype=date);
run;
%let thismonthstart=%sysfunc(today(),fom.);
*or %let thismonthstart=%sysfunc(intnx(month,&sysfunc(today),0),date9.);
%put &thismonthstart;
data _null_;
dt1=intnx('week.6',intnx('month',"&thismonthstart"d,0,'end'),0);
put dt1;
run;
You have generated a macro variable (always a character string) with 01NOV2011 as its value.
To use that as a date literal in the SAS code you need quote it and append the letter D.
MonthEnd1=intnx('month',"&ThisMonthStart"d,0,'end');
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.