Dynamic last dat of the month using year and month

[vnreddy]

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

[PaigeMiller]

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.

This is confusing. You talk about months, then show week end dates. Please explain.

[ballardw · Accepted solution]

---

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