DATA Step, Macro, Functions and more

How to derive Last day of last month?

Accepted Solution Solved
Reply
Super Contributor
Posts: 617
Accepted Solution

How to derive Last day of last month?

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

 


Accepted Solutions
Solution
‎04-05-2018 05:24 AM
Super Contributor
Posts: 448

Re: How to derive Last day of last month?

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;
Thanks,
Suryakiran

View solution in original post


All Replies
SAS Moderator
Posts: 46

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

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.
    use_buttons.png
  • 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.

 

edit_post.png

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 @KurtBremser

.
Super User
Posts: 9,550

Re: How to derive Last day of last month?

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?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super Contributor
Posts: 617

Re: How to derive Last day of last month?

Posted in reply to KurtBremser
I want this program to use the current date.
Super User
Super User
Posts: 9,193

Re: How to derive Last day of last month?

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.

Super Contributor
Posts: 448

Re: How to derive Last day of last month?

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;
Thanks,
Suryakiran
Super Contributor
Posts: 617

Re: How to derive Last day of last month?

Posted in reply to SuryaKiran
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.
Super User
Posts: 13,006

Re: How to derive Last day of last month?


@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.

Solution
‎04-05-2018 05:24 AM
Super Contributor
Posts: 448

Re: How to derive Last day of last month?

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;
Thanks,
Suryakiran
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 99 views
  • 3 likes
  • 6 in conversation