topic Re: How to derive Last day of last month? in SAS Programming

How to derive Last day of last month?

Babloo — Wed, 04 Apr 2018 14:01:08 GMT

Appreciate if someone of you help me understand the macro variable/function to overwrite &Prompt_Execution_dt. I was in the process of updating the old programs and when I execute this code in EG it asks for a prompt before it produce the result. However I was asked to not to use this approach now and I'm not certain what can I write in the below program instead of &Prompt_Execution_dt

34         Data _null_;
35         	FORMAT FirstDayofReportingMonth_dt LastDayofReportingMonth_dt REPORTING_DT DATE9.;
36         	FORMAT REPORTING_DTTM DATETIME23.;
37         
38         	FirstDayofReportingMonth_dt = INTNX ('MONTH', "&Prompt_Execution_dt"d, -1, 'B');
39         	LastDayofReportingMonth_dt = INTNX ('MONTH', "&Prompt_Execution_dt"d, -1, 'E');
40         	call symput("FirstDayofReportingMonth", put (FirstDayofReportingMonth_dt, yymmddn8.));
41         	call symput("LastDayofReportingMonth", put (LastDayofReportingMonth_dt, yymmddn8.));
42         
43         	REPORTING_DT = LastDayofReportingMonth_dt;                                                                                 

44         	REPORTING_DTTM = INPUT (PUT (REPORTING_DT, DATE9.) || ':23:59:59', DATETIME18.);
45         	call symput("reporting_date", put (REPORTING_DT, date9.));
46         	call symput("reporting_datetime", left (put (REPORTING_DTTM, datetime23.)));
47         	call symput("reporting_num_date", put (REPORTING_DT, yymmddn8.));
48         Run;

NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds
      

49         
50         %put FirstDayofReportingMonth = &FirstDayofReportingMonth;
FirstDayofReportingMonth = 20180301
51         %put LastDayofReportingMonth  = &LastDayofReportingMonth;
LastDayofReportingMonth  = 20180331
52         %put reporting_date           = &reporting_date;
reporting_date           = 31MAR2018
53         %put reporting_datetime       = &reporting_datetime;
reporting_datetime       = 31MAR2018:23:59:59
54         %put reporting_num_date       = &reporting_num_date;
reporting_num_date       = 20180331

Re: How to derive Last day of last month? [how to improve your question]

Community_Guide — Wed, 04 Apr 2018 14:02:44 GMT

Hello @Babloo,

Your question requires more details before experts can help. Can you revise your question to include more information?

Review this checklist:

Specify a meaningful subject line for your topic. Avoid generic subjects like "need help," "SAS query," or "urgent."
When appropriate, provide sample data in text or DATA step format. See this article for one method you can use.
If you're encountering an error in SAS, include the SAS log or a screenshot of the error condition. Use the Photos button to include the image in your message.
It also helps to include an example (table or picture) of the result that you're trying to achieve.

To edit your original message, select the "blue gear" icon at the top of the message and select Edit Message. From there you can adjust the title and add more details to the body of the message. Or, simply reply to this message with any additional information you can supply.

SAS experts are eager to help -- help them by providing as much detail as you can.

This prewritten response was triggered for you by fellow SAS Support Communities member @Kurt_Bremser

Re: How to derive Last day of last month?

Kurt_Bremser — Wed, 04 Apr 2018 14:05:05 GMT

Forget the @Community_Guide post, I just hit the wrong button.

If you shall not prompt for a date, then which date should be used?

Re: How to derive Last day of last month?

RW9 — Wed, 04 Apr 2018 14:17:03 GMT

Why do you need all that in macro variables? If you know the formula is index(...), then just use that in your code? You seem to be putting a lot of effort into calculating things, then converting to text, then converting back to number to do some more calculations, then saving in text again, none of which seems to give any benefit.

Re: How to derive Last day of last month?

SuryaKiran — Wed, 04 Apr 2018 14:44:54 GMT

Hi,

Do you want to give date explicitly in your program instead of using the prompt? Then replace the &Prompt_Execution_dt with a macro variable that holds date value.

%LET Execution_dt=04APR2018;

Data _null_;
FORMAT FirstDayofReportingMonth_dt LastDayofReportingMonth_dt REPORTING_DT DATE9.;
FORMAT REPORTING_DTTM DATETIME23.;
FirstDayofReportingMonth_dt = INTNX ('MONTH', "&Execution_dt"d, -1, 'B');
LastDayofReportingMonth_dt = INTNX ('MONTH', "&Execution_dt"d, -1, 'E');
call symput("FirstDayofReportingMonth", put (FirstDayofReportingMonth_dt, yymmddn8.));
        	call symput("LastDayofReportingMonth", put (LastDayofReportingMonth_dt, yymmddn8.));     
REPORTING_DT = LastDayofReportingMonth_dt;                                                                                 
      	REPORTING_DTTM = INPUT (PUT (REPORTING_DT, DATE9.) || ':23:59:59', DATETIME18.);
call symput("reporting_date", put (REPORTING_DT, date9.));
       	call symput("reporting_datetime", left (put (REPORTING_DTTM, datetime23.)));
        	call symput("reporting_num_date", put (REPORTING_DT, yymmddn8.));
Run;

Re: How to derive Last day of last month?

Babloo — Wed, 04 Apr 2018 14:48:49 GMT

I don't want to hard code anything here. I just want this program to
automatically take the current date instead of asking for a prompt.

Re: How to derive Last day of last month?

Babloo — Wed, 04 Apr 2018 14:49:49 GMT

I want this program to use the current date.

Re: How to derive Last day of last month?

ballardw — Wed, 04 Apr 2018 15:32:42 GMT

@Babloo wrote:
I don't want to hard code anything here. I just want this program to
automatically take the current date instead of asking for a prompt.

In a data step:

data _null_;
   lastmonthend = intnx('month',today(),-1,'E');
   put lastmonthend= mmddyy10.;
run;

However I don't know what you want, or if you even NEED, a macro variable to look like. The example data step should be easy enough to generate a macro variable if needed in the desired form.

One thing to consider about dates and macro variables: Are people looking at the variable or is it used in calculations internal to the process? Values in formatted date appearance such as "01MAR2018" or "03/01/2018" or "20180301" are more for humans to look at and for actual calculations in SAS data steps or selection filters involving dates then the numeric value, such as LASTMONTHEND above without a format applied is often preferred.

Re: How to derive Last day of last month?

SuryaKiran — Wed, 04 Apr 2018 17:04:09 GMT

Just replace the macro variable with TODAY()

Data _null_;
FORMAT FirstDayofReportingMonth_dt LastDayofReportingMonth_dt REPORTING_DT DATE9.;
FORMAT REPORTING_DTTM DATETIME23.;
FirstDayofReportingMonth_dt = INTNX ('MONTH', TODAY(), -1, 'B');
LastDayofReportingMonth_dt = INTNX ('MONTH', TODAY(), -1, 'E');
	call symput("FirstDayofReportingMonth", put (FirstDayofReportingMonth_dt, yymmddn8.));
    call symput("LastDayofReportingMonth", put (LastDayofReportingMonth_dt, yymmddn8.));     
REPORTING_DT = LastDayofReportingMonth_dt;                                                                                 
REPORTING_DTTM = INPUT (PUT (REPORTING_DT, DATE9.) || ':23:59:59', DATETIME18.);
	call symput("reporting_date", put (REPORTING_DT, date9.));
    call symput("reporting_datetime", left (put (REPORTING_DTTM, datetime23.)));
    call symput("reporting_num_date", put (REPORTING_DT, yymmddn8.));
Run;