DATA Step, Macro, Functions and more

Create a list of date of each end of month

Reply
Occasional Contributor
Posts: 8

Create a list of date of each end of month

[ Edited ]

Hi,

I would like to create a sas macro who will give us a list containing all end of month according to the current date.

For example:

  • If the macro is launch in March, we will have list_of_date=('31/01/2017', '28/02'2017').
  • If the macro is launch in June, we will have list_of_date=('31/01/2017', '28/02'2017', '31/03/2017','30/04/2017', '31/05/2017')
  • If the macro is launch in junuary (of year N+1), we will have the end of all month of the previous year.

Thanks for all.

Super User
Posts: 6,938

Re: Create a list of date of each end of month

What do you intend to do with the list? I ask because for a lot of uses your date format would not be very useful.

And I do believe some of your dates in the June example have the wrong month.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 8

Re: Create a list of date of each end of month

Hi,
sorry it was a mistake.
The list is for filtering a column who have values like('31/01/2017', '28/02'2017', '31/03/2017','30/04/2017', '31/05/2017').
Occasional Contributor
Posts: 8

Re: Create a list of date of each end of month

Here is the code.

 

%let list_of_date=periode in ('31/01/2017') ; 

data his_cum;
set REF_TREF.HIS_opn_stats;
   where &list_of_date.;
run;

Each month i manually add the end of the month to the list_of_date macro_variable.

Super Contributor
Posts: 474

Re: Create a list of date of each end of month

Hi.

 

I would make a macro "function" to build the desired list based on current date like this,

 

%macro list_of_dates(NOW,FORMAT);
%let _DAY=%eval(%sysfunc(intnx(year,&NOW,-1,e))+1);
%let _NOW=%eval(%sysfunc(intnx(month,&NOW,0,b))-1);
%if &_DAY gt &_NOW %then %let _DAY=%sysfunc(intnx(year,&NOW,-1,b));
%do _I=&_DAY %to &_NOW %by 31;
"%sysfunc(putn(%sysfunc(intnx(month,&_I,0,end)),&FORMAT))"
%end;
%mend list_of_dates; * build a IN list of dates;

data want;
set have;
    where periode in ( %list_of_dates(%sysfunc(today()),yymmdds10.) );
run;

%sysfunc(today()) wil retrieve today's date

yymmdds10. is the date format for YYYY/MM/DD (you can change it any know SAS format)

 

Hope it helps.

 

Daniel Santos @ www.cgd.pt

Super User
Posts: 10,500

Re: Create a list of date of each end of month


foxrol94 wrote:
Hi,
sorry it was a mistake.
The list is for filtering a column who have values like('31/01/2017', '28/02'2017', '31/03/2017','30/04/2017', '31/05/2017').

You don't mention HOW you actually use the list, to keep or drop.

Note that this line of code in a data step or proc sql will only keep records that the character date variable is for the last day of the month

 

where input(datevar,ddmmyy10.) = intnx('month',input(datevar,ddmmyy10.),0,'E');

 

This would exclude

where input(datevar,ddmmyy10.) ne intnx('month',input(datevar,ddmmyy10.),0,'E');

Super User
Posts: 6,938

Re: Create a list of date of each end of month


foxrol94 wrote:
Hi,
sorry it was a mistake.
The list is for filtering a column who have values like('31/01/2017', '28/02'2017', '31/03/2017','30/04/2017', '31/05/2017').

Your list suggests that you have dates stored as strings of length 10, with a DMY sequence. This is sub-optimal for several reasons: you can't use any SAS date functions directly, and the sort sequence will not be chronological. And you need to create your list for the "in" condition with quotes, which unnecessarily complicates things. With SAS dates, you can create a simple list of numbers.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 7,363

Re: Create a list of date of each end of month

The following could probably be simplified, but does what you want (I think):

 

%macro getdates();
  data _null_;
    length want $255;
    end=intnx('month',today(),-1,'E');
    want='';
    do i=1 to month(end);
      date=catt("'",put(intnx('month',mdy(i,1,year(end)),0,'E'),yymmdds10.),"'");
      want=catx(',',want,date);
    end;
    want=catt('periode in (',want,')');
    call symput('list_of_date',want);
  run;
%mend getdates;

%getdates
%put &list_of_date.;

Art, CEO, AnalystFinder.com

 

Super User
Posts: 9,681

Re: Create a list of date of each end of month

data x;
 do i=1 to month(today());
  date=intnx('month',mdy(i,1,year(today())),0,'e');
  output;
 end;
 format date date9.;
run;
proc sql noprint;
select quote(put(date,date9.)) into : list_of_date separated by ','
 from x;
quit;


%let list_of_date=(&list_of_date);

%put &list_of_date ;
Ask a Question
Discussion stats
  • 8 replies
  • 216 views
  • 1 like
  • 6 in conversation