BookmarkSubscribeRSS Feed
foxrol94
Fluorite | Level 6

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.

9 REPLIES 9
Kurt_Bremser
Super User

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.

foxrol94
Fluorite | Level 6
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').
foxrol94
Fluorite | Level 6

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.

DanielSantos
Barite | Level 11

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

ballardw
Super User

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

Kurt_Bremser
Super User

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

art297
Opal | Level 21

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

 

Ksharp
Super User
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 ;
NiyiAwe1
Fluorite | Level 6

/*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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 6787 views
  • 5 likes
  • 7 in conversation