Find the last day of the month

[SASlearner97]

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

[PaigeMiller]

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.

[SASlearner97]

Thanks for your feedback

you are right i will edit the question;

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

[andreas_lds]

---

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

[SASlearner97]

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

[PaigeMiller]

---

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

[SASlearner97]

i have a table of daily data

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

[PaigeMiller]

---

@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

[SASlearner97]

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

thats why It shows 27/10/2020

[andreas_lds]

---

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

[SASlearner97]

yes

[andreas_lds]

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

[CurtisMackWSIPP]

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

[SASlearner97]

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

[Kurt_Bremser]

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