Problem with MDY function to take the previous month

[Mick_lb]

Hello everyone, 

From a date, I want to take the last day of the previous month. And after that, display the month and the year (of the previous month).

The problem is, my code is working for every month except January.

%let date="01JAN2017:00:00:00"dt;
%put ===> &date.;

data _null_;
last_day = day(intnx('month', datepart(&date.), -1, "end"));
CALL SYMPUT("last_day",PUT(last_day,DATE9.));
/*To display the month*/
p_month=MDY(month(datepart(&date.))-1, last_day, year(datepart(&date.))); /*Error here*/
CALL SYMPUT("p_month",PUT(p_month,FRADFMN.));
/*To display the year*/
p_year=MDY(month(datepart(&date.))-1, last_day, year(datepart(&date.))); /*Error here*/
CALL SYMPUT("p_year",PUT(p_year,YEAR4.));
run;
%put ===> &last_day.;
%put ===> &p_month.;
%put ===> &p_year.;

For January, I got the following error :

NOTE: Invalid argument for MDY function (0,31,2017) at line 31 column 9.

NOTE: Invalid argument for MDY function (0,31,2017) at line 34 column 8.

last_day=31 p_month=. p_year=. _ERROR_=1 _N_=1

NOTE: Mathematical operations could not be performed at the following places. The results of the operations have been set to missing values.

How to tell SAS not to take month = 0 when it's January ?

Thank you in advance for your help !

Best regards,

[Kurt_Bremser]

Overcomplicating things by a mile.

data _null_;
last_date = intnx('month', datepart(&date.), -1, "end");
CALL SYMPUT("last_day",PUT(day(last_date),2.));
/*To display the month*/
CALL SYMPUT("p_month",PUT(month(last_date),2.);
/*To display the year*/
CALL SYMPUT("p_year",PUT(year(last_date),4.));
run;

[ed_sas_member]

Hi @Mick_lb 

You need to be very careful with dates in macro variables.

It should not be formatted to be used for further calculation

Typically, 01JAN2017 (which is human-readable) is stored as the numeric value 20820 by SAS (= number of days since 01JAN1960)

Best,

%let date=01JAN2017:00:00:00;
%put ===> &date;

%let date_2=%sysfunc(datepart(%sysfunc(inputn(&date,datetime18))));

%let last_day=%sysfunc(day(%sysfunc(intnx(month,&date_2,-1,end))));
%let p_month=%sysfunc(month(%sysfunc(intnx(month,&date_2,-1,end))));
%let p_year=%sysfunc(year(%sysfunc(intnx(month,&date_2,-1,end))));

%put ===> &last_day.;
%put ===> &p_month.;
%put ===> &p_year.;

[Reeza]

INTNX() is used to move the date back one month, you can not use base math here because you cycle from 1 to 12 with month. You can use MOD() if you want to use math type functions, but using INTNX() appropriately is a better approach. 

---

@Mick_lb wrote:

Hello everyone, 

From a date, I want to take the last day of the previous month. And after that, display the month and the year (of the previous month).

The problem is, my code is working for every month except January.

%let date="01JAN2017:00:00:00"dt;
%put ===> &date.;

data _null_;
last_day = day(intnx('month', datepart(&date.), -1, "end"));
CALL SYMPUT("last_day",PUT(last_day,DATE9.));
/*To display the month*/
p_month=MDY(month(datepart(&date.))-1, last_day, year(datepart(&date.))); /*Error here*/
CALL SYMPUT("p_month",PUT(p_month,FRADFMN.));
/*To display the year*/
p_year=MDY(month(datepart(&date.))-1, last_day, year(datepart(&date.))); /*Error here*/
CALL SYMPUT("p_year",PUT(p_year,YEAR4.));
run;
%put ===> &last_day.;
%put ===> &p_month.;
%put ===> &p_year.;

For January, I got the following error :

NOTE: Invalid argument for MDY function (0,31,2017) at line 31 column 9.

NOTE: Invalid argument for MDY function (0,31,2017) at line 34 column 8.

last_day=31 p_month=. p_year=. _ERROR_=1 _N_=1

NOTE: Mathematical operations could not be performed at the following places. The results of the operations have been set to missing values.

How to tell SAS not to take month = 0 when it's January ?

Thank you in advance for your help !

Best regards,

---