topic Re: Problem with MDY function to take the previous month in New SAS User

Problem with MDY function to take the previous month

Mick_lb — Fri, 13 Dec 2019 10:04:30 GMT

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,

Re: Problem with MDY function to take the previous month

Kurt_Bremser — Fri, 13 Dec 2019 10:17:18 GMT

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;

Re: Problem with MDY function to take the previous month

ed_sas_member — Fri, 13 Dec 2019 10:29:28 GMT

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

Re: Problem with MDY function to take the previous month

Reeza — Fri, 13 Dec 2019 16:41:12 GMT

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,