topic SAS Date format for Start of the month in SAS Procedures

SAS Date format for Start of the month

Wickywick — Tue, 29 Nov 2011 22:26:06 GMT

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

Re: SAS Date format for Start of the month

FriedEgg — Tue, 29 Nov 2011 22:52:35 GMT

%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

Re: SAS Date format for Start of the month

Peter_C — Tue, 29 Nov 2011 23:19:11 GMT

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

where timeStamp >= "&thisMonthStart:0:0:0"dt

when you are testing against a date-time value

Re: SAS Date format for Start of the month

art297 — Tue, 29 Nov 2011 23:24:18 GMT

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.

SAS Date format for Start of the month

Wickywick — Wed, 30 Nov 2011 00:03:38 GMT

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=;

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 ! ;

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);

_____

NOTE: Line generated by the macro variable "THISMONTHSTART".

17 01NOV2011

_______

18 DT2=intnx('week.6',intnx('month',&ThisMonthStart.,-1,'end'),0);

_____

NOTE: Line generated by the macro variable "THISMONTHSTART".

18 01NOV2011

_______

19 MonthEnd1=intnx('month',&ThisMonthStart.,0,'end');

_____

NOTE: Line generated by the macro variable "THISMONTHSTART".

19 01NOV2011

_______

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

29 %LET _CLIENTTASKLABEL=;

30 %LET _EGTASKLABEL=;

31 %LET _CLIENTPROJECTNAME=;

32 %LET _SASPROGRAMFILE=;

34 ;*';*";*/;quit;run;

35 ODS _ALL_ CLOSE;

38 QUIT; RUN;

SAS Date format for Start of the month

FriedEgg — Wed, 30 Nov 2011 00:11:47 GMT

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;

SAS Date format for Start of the month

Tom — Wed, 30 Nov 2011 00:48:25 GMT

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');

Re: SAS Date format for Start of the month

Peter_C — Wed, 30 Nov 2011 17:17:39 GMT

Art

scary!:smileyalert:

and simply needs &sysdate replaced as demonstrated by others, with

%sysfunc( today(), date9)

peter