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:
Thanks for all.
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.
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.
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
@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');
@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.
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
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 ;
/*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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.