@vnreddy wrote:
Hi,
I am having difficulty in creating a dynamic last day of the month date9. format by using yyyymm format.
I have created below code, and at present i am manually changing end_date value (e.g., -51, -52, so on). I want to eliminate this and macro should pick the last day of the month from %check(201701 201702)
end_date values in dec_data dataset will in 03JAN2017, 10JAN2017, 31JAN2017 so on format.
%macro check(months);
%let months_count = %sysfunc(countw(&months.,' '));
%do month_ind=1 %to &months_count.;
%let yyyymm = %scan(&months.,&month_ind.,' ');
%let yymm=%substr(&yyyymm.,3,4);
%let year=%substr(&yyyymm.,1,4);
%let month=%substr(&yyyymm.,5,2);
%put &=yyyymm.;
%put &=yymm.;
%put &=year.;
%put &=month.;
%let end_date = %sysfunc(intnx(month,%sysfunc(today()),-51,end), date9.);
%put &=end_date;
libname kc&yymm. "/sas/Prod/Data/&yyyymm.";
data logic_&yymm.;
set kc&yymm..dec_data;
if date ne . and date <= "&end_date"d then IND = 1;
else IND = 0;
run;
%end;
%mend;
%check(201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712);
Thank you,
vnreddy
Not sure if this is exactly what you are looking for, just the end_date bit as I have no clue what you data set looks like.
%macro check(months);
%let months_count = %sysfunc(countw(&months.,' '));
%do month_ind=1 %to &months_count.;
%let yyyymm = %sysfunc(inputn(%scan(&months.,&month_ind.,' '),yymmn6.));
%let end_date = %sysfunc(intnx(month,&yyyymm.,0,end), date9.);
%put end_date for %scan(&months.,&month_ind.,' ') is: &end_date.;
%end;
%mend;
%check(201701 201702 201703 201704 201705 201706 201707 201708 201709 201710 201711 201712);
Generally if you are going to use a value in comparisons it is often better not to format the value, i.e. the "&end_date."d but use the unformatted number &end_date. and leave off the ", date9." when creating it.
... View more