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');
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.