topic Re: Create a list of date of each end of month in SAS Programming

Create a list of date of each end of month

foxrol94 — Tue, 14 Feb 2017 15:41:26 GMT

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.

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

Kurt_Bremser — Tue, 14 Feb 2017 15:29:21 GMT

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.

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

foxrol94 — Tue, 14 Feb 2017 15:45:06 GMT

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

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

foxrol94 — Tue, 14 Feb 2017 15:49:23 GMT

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.

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

art297 — Tue, 14 Feb 2017 16:13:50 GMT

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

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

DanielSantos — Tue, 14 Feb 2017 16:39:50 GMT

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

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

ballardw — Tue, 14 Feb 2017 17:58:56 GMT

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

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

Kurt_Bremser — Tue, 14 Feb 2017 18:35:15 GMT

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

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

Ksharp — Wed, 15 Feb 2017 03:16:18 GMT

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 ;

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

NiyiAwe1 — Wed, 17 Jan 2018 21:11:16 GMT

/*USING MACRO*/
%MACRO MONTHS;
%DO M=1 %TO 40;
DATA dateR;
FORMAT SEE DATE9.;
SEE = INTNX('MONTH',TODAY(),-&M,'B');
RUN;
PROC APPEND
    DATA=dateR
   BASE=want_data
   FORCE;
%END;
%MEND MONTHS;
%MONTHS