topic Re: Find the last day of the month in New SAS User

Find the last day of the month

SASlearner97 — Tue, 27 Oct 2020 13:55:57 GMT

I have a daily data

date var value

01/01/2013 a1 0

...

27/10/2020 a3 12

I want to get the data stored in the last day of each month for every year between the first date and the last date :

date var value

31/01/2013 a1 5

28/02/2013 a2 4

.....

27/10/2020 a3 12

i thought of making a call symput of the dates but i dont know how to

please help

thank you

Re: Find the last day of the month

PaigeMiller — Tue, 27 Oct 2020 13:39:31 GMT

CALL SYMPUT has nothing to do with this. You need the INTNX function to get the last day of the month.

Something like this:

last_day_of_month=intnx('month',date,0,'e');
format last_day_of_month ddmmyys10.;

I don't understand why your input data doesn't match your output. In the Input data, your last value is 27/10/2020 while the output data has as its last value 30/09/2020.

Re: Find the last day of the month

SASlearner97 — Tue, 27 Oct 2020 13:44:51 GMT

Thanks for your feedback

you are right i will edit the question;

actually i want to get the data stored in these dates ...

Re: Find the last day of the month

CurtisMackWSIPP — Tue, 27 Oct 2020 13:46:05 GMT

You weren't clear about the environment in which you want to do this. Does this come close? The Macro could be eliminated.

%macro EOM(firstdata,lastdate);
  data dates;
    format date date9.;
    date = intnx("MONTH",input("&firstdata",date9.),0,"end");
    do until (date > input("&lastdate",date9.));
      output;
      date = intnx("MONTH",date,1,"end");
    end;
  run;
%mend;
%EOM(05JAN2020,14JUN2020);

Re: Find the last day of the month

andreas_lds — Tue, 27 Oct 2020 13:50:16 GMT

@SASlearner97 wrote:
Thanks for your feedback

you are right i will edit the question;

actually i want to get the data stored in these dates ...

Sorry, not clear what you mean with the last sentence. Please don't edit your initial post as this makes it impossible to understand all the answers given before the change.

Re: Find the last day of the month

PaigeMiller — Tue, 27 Oct 2020 13:53:42 GMT

@SASlearner97 wrote:
Thanks for your feedback

you are right i will edit the question;

actually i want to get the data stored in these dates ...

Now that you have edited the output data, the last value in the output data is NOT the last day of a month. Could you please explain?

Re: Find the last day of the month

SASlearner97 — Tue, 27 Oct 2020 14:02:59 GMT

i have a table of daily data

i want to select the data of only the end of months for each year

Re: Find the last day of the month

PaigeMiller — Tue, 27 Oct 2020 14:05:39 GMT

@SASlearner97 wrote:

i want to select the data of only the end of months for each year

But your output (as modified by you earlier) doesn't do this. It shows 27/10/2020

Re: Find the last day of the month

SASlearner97 — Tue, 27 Oct 2020 14:15:03 GMT

27/10/2020 is the last day of month in this example

thats why It shows 27/10/2020

Re: Find the last day of the month

SASlearner97 — Tue, 27 Oct 2020 14:17:13 GMT

i have a table of daily data

i want to select the data of only the end of months for each year

the ends of months maybe 28/03/2015
31/12/2015
30/04/2016 ..

Re: Find the last day of the month

SASlearner97 — Tue, 27 Oct 2020 14:27:47 GMT

yes it comes close

my table got some months that ends in 27 or less .. like i have 29/12/2011 for some var

how to find the ends of months of dates in my table please

Thank you

Re: Find the last day of the month

Kurt_Bremser — Tue, 27 Oct 2020 14:39:30 GMT

And what is the rule for months that do not end on the last calendar day of that month, but a previous day?

Re: Find the last day of the month

andreas_lds — Wed, 28 Oct 2020 05:17:14 GMT

@SASlearner97 wrote:
i have a table of daily data

i want to select the data of only the end of months for each year

So you want to select the all observations having date = max(date) by month/year?

Re: Find the last day of the month

SASlearner97 — Wed, 28 Oct 2020 07:45:23 GMT

yes

Re: Find the last day of the month

andreas_lds — Wed, 28 Oct 2020 08:29:32 GMT

Try something like:

data bob;
  set have;
  group_date = intnx('MONTH', date, 0 'b');
run;

proc sort data=bob;
  by group_date date;
run;

data want;
  set bob;
  by group_date date;
  if last.date;
run;

If you want tested code, post data in usable form (data step with datalines, no attachments).

Re: Find the last day of the month

mkeintz — Wed, 28 Oct 2020 12:57:01 GMT

I'm late coming into this discussion, but it appears to me that you want the last observed date in each month, which I interpret differently than the ordinary last date.

If so, then you can run a program that looks ahead one record to see if the next record is in a different month than the current record:

proc sort data=have out=need;
  by date;
run;

data want (drop=nxt_date);
  merge need
        need (firstobs=2 keep=date rename=(date=nxt_date));
  if intck('month',date,nxt_date);
run;

If your original dataset (named "have" here) is already sorted, skip the proc sort, and apply the merge statement to have instead of need.

The merge statement combines each observation with a single, renamed variable (date=nxt_date) from the following observation (that's from the "firstobs=2" parameter). This allows the intck function to count the number of months between date and nxt_date. Only non-zero results pass the filter - yielding the last observed date of the month.

Re: Find the last day of the month

SASlearner97 — Wed, 28 Oct 2020 13:59:00 GMT

Thank you so much

in my var column i have 4 kinds of observations like :

var
a1
a2
a3
a4
....

when i tried your code it went like :
var date
a1 31/01/2013
a1
a1
a1
...
the dates are correct but the column var is wrong ..

Re: Find the last day of the month

mkeintz — Thu, 29 Oct 2020 18:25:31 GMT

@SASlearner97 wrote:
Thank you so much

in my var column i have 4 kinds of observations like :

var
a1
a2
a3
a4
....

when i tried your code it went like :
var date
a1 31/01/2013
a1
a1
a1
...
the dates are correct but the column var is wrong ..

Then I don't understand what you want, and probably I don't understand what you are starting with. Please provide, in the form of a SAS data step, a sample of the data you are starting with (call it data set HAVE), and a sample of what it should provide, also in the from of a data step. Then I can provide code to work with what you have, as opposed to guessing what you have.

Help us help you.