topic Re: automatically find previous month and a year in SAS Programming

automatically find previous month and a year

tparvaiz — Wed, 26 Apr 2017 18:26:27 GMT

Hi, I have a program that uses hardcoded values.. something like this

%let P_month = 03;
%let P_year = 2017;

where P_month is a previous month and P_year is the year associated to the previous month.

can you please advise how to automatically convert it using sysfunc

Thanks in advance

Re: automatically find previous month and a year

collinelliot — Wed, 26 Apr 2017 19:22:31 GMT

I don't fully understand what you want. I'm assuming you have a current month and year and you want to know what they are if you shift one month back? This example uses January so you can see that it shifts to December of the previous year.

%let C_month = 01;
%let C_year = 2017;

%let P_month = %sysfunc(month(%sysfunc(intnx(month, %sysfunc(mdy(&c_month, 1, &c_year)), -1))));
%let P_year = %sysfunc(year(%sysfunc(intnx(month, %sysfunc(mdy(&c_month, 1, &c_year)), -1))));
%put The Current Month and Year are: &c_month, &c_year;
%put The Previous Month and Year (based on one month back) are: &p_month, &p_year;

Re: automatically find previous month and a year

ballardw — Wed, 26 Apr 2017 19:32:32 GMT

I would not use SYSfunc as the result is going to be ugly. Just as in your other post https://communities.sas.com/t5/Base-SAS-Programming/auto-dates/m-p/353496#M82521

The intnx function as used in the other post works given any date. If you need previous from today then the base date in the function can be the function Today().

The use the function Year or Month on the result.

Maybe

data _null_;

call symputx('P_Month', month(intnx('month',today(),-1));

run;

does what you are requesting. Year is left as an exercise for the interested reader.

Note that you will have to nest at least 3 levels of %sysfunc to accomplisth the same thing, one for each function called: month, intnx, today. The resulting code with all of the extra () and such is harder to read and more likely to be fragile.

Re: automatically find previous month and a year

tparvaiz — Wed, 26 Apr 2017 20:28:07 GMT

collinelliot, thanks for your assistance. let me clearify

we run monthly reports (usually, 2nd day of the month) covering the previous month. Reports are working fine when we use set valus such as, so for Jan report (we will run the report on Feb 2nd) and values will be set as follows

%let p_month = 01;
%let p_year = 2017;

on March 2nd will change the values as follows before running the reports

%let p_month = 02;
%let p_year = 2017;

can you please modify your code to take systems date and do it automatically... something like this

%let P_month = %sysfunc(month(%sysfunc(intnx(month, %sysfunc(mdy(TODAY_MONTH, 1, TODAY_YEAR)), -1))));
%let P_year = %sysfunc(year(%sysfunc(intnx(month, %sysfunc(mdy(TODAY_MONTH, 1, TODAY_YEAR))), -1))));

Thanks again for your assistance

Re: automatically find previous month and a year

collinelliot — Wed, 26 Apr 2017 21:02:16 GMT

I see. It's a matter of changing the mdy(..) to today()... But does it need to be done with %let / %sysfunc? I think @ballardw makes a good suggestion that would give you more legible code.

That said:

%let P_month = %sysfunc(month(%sysfunc(intnx(month, %sysfunc(today()), -1))));
%let P_year = %sysfunc(year(%sysfunc(intnx(month, %sysfunc(today()), -1))));
%put The Previous Month and Year (based on one month back) are: &p_month, &p_year;

Re: automatically find previous month and a year

tparvaiz — Wed, 26 Apr 2017 21:14:52 GMT

Here is what I've found... seems like my issue is with the format of the date (date1 and date2)... here are the 2 codes that I ran with the output

----------ORIGINAL CODE with fixed values -------------

438 %let month = 03;
439 %let year = 2017;
440 %let date1 = '01Mar2017'd;
441 %let date2 = '01Apr2017'd;
444 %put &month, &year, &date1, &date2;
03, 2017, '01Mar2017'd, '01Apr2017'd

----------MODIFIED CODE to automatically select values -------------

451 %let month =%sysfunc(intnx(month, %sysfunc(today()), -1), MONTH.);
452 %let year =%sysfunc(intnx(month, %sysfunc(today()), -1), YEAR.);
453
454 %let date1 =
454! %str(%')%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),-1)),date9.))%str(%')d;
455 %let date2 = %str(%')%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),
455! 0)),date9.))%str(%')d;
457 %put &month, &year, &date1, &date2;
3, 2017, '01MAR2017'd, '01APR2017'd

----------Sample code -------------

data data.active;
set temp.v_table_all(keep = field1 field2
where = (&date1 <= begindttm <&date2);
run;

when I run my code with the fixed values for date1 and date2, it works fine...

but it is not working with modified code. noticed that my modified code is returning month in upper caps and the fixed one is using lower caps. can that be an issue? and how to fix it

thanks

Re: automatically find previous month and a year

collinelliot — Wed, 26 Apr 2017 21:32:51 GMT

Looking at what you're trying to do, I think the following at least comes close. your "begindttm" variable, though, makes me wonder if you have a date value or a datetime value that you're comparing to. If it's not date, you'll need to change a few things.

%let cur_date = %sysfunc(today(), date9.);  <-- Or yes, doh, just &sysdate9...
%let pre_date = %sysfunc(putn(%sysfunc(intnx(month, %sysfunc(today()), -1, same)), date9.));

%put &cur_date;
%put &pre_date;

data data.active;
set temp.v_table_all(keep = field1 field2 
where = ("&pre_date"d <= begindttm < "&cur_date"d);
run;

Re: automatically find previous month and a year

Astounding — Wed, 26 Apr 2017 21:31:01 GMT

No matter which way you end up going (DATA step, %SYSFUNC), I suggest you start with this:

%let refdate = "&sysdate9"d;

Then build the month and year from &REFDATE.

Why? For practical purposes in running a monthly report, &SYSDATE9 and TODAY() give you the same day. But consider what would happen if you ever want to backdate your report and get the report for 2 months ago instead of last month. Now you only have to change one line of code, hard-coding a value in the %LET statement.

Re: automatically find previous month and a year

ballardw — Wed, 26 Apr 2017 21:49:08 GMT

@tparvaiz wrote:

Here is what I've found... seems like my issue is with the format of the date (date1 and date2)... here are the 2 codes that I ran with the output

----------ORIGINAL CODE with fixed values -------------

438 %let month = 03;
439 %let year = 2017;
440 %let date1 = '01Mar2017'd;
441 %let date2 = '01Apr2017'd;
444 %put &month, &year, &date1, &date2;
03, 2017, '01Mar2017'd, '01Apr2017'd

----------MODIFIED CODE to automatically select values -------------

451 %let month =%sysfunc(intnx(month, %sysfunc(today()), -1), MONTH.);
452 %let year =%sysfunc(intnx(month, %sysfunc(today()), -1), YEAR.);
453
454 %let date1 =
454! %str(%')%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),-1)),date9.))%str(%')d;
455 %let date2 = %str(%')%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),
455! 0)),date9.))%str(%')d;
457 %put &month, &year, &date1, &date2;
3, 2017, '01MAR2017'd, '01APR2017'd

----------Sample code -------------

data data.active;
set temp.v_table_all(keep = field1 field2
where = (&date1 <= begindttm <&date2);
run;

when I run my code with the fixed values for date1 and date2, it works fine...

but it is not working with modified code. noticed that my modified code is returning month in upper caps and the fixed one is using lower caps. can that be an issue? and how to fix it

thanks

And did you tryit with the code suggested in the previous post such as:

data _null_;
   call symputx('YYYMM',put(intnx('month',today(),-1),yymmn6.));
   call symputx('date1',intnx('month',today(),-1,'b'));
   call symputx('date2',intnx('month',today(),0,'b'));
run;

Experience says that using quotes as part of macro variables often has issues, besides requiring ugly code to put them in.

And for the show use of date1 and date2 you do not need the date literal form of the date. The numeric version show in the above data _null_ step should work just fine.

Note that your posted example data step will throw errors because of missing ) AND using where with a variable that does not appear on the KEEP portion of the datastep options. Try:

data data.active;
   set temp.v_table_all(keep = field1 field2 begindttm
               where = (&date1 <= begindttm <&date2));
run;

BUT there may be yet more fun, you are comparing dates to a variable named Begindttm. I am afraid that Begindttm may actually be a a DATETIME variable and if so nothing is likely to be selected as dates are measured in days and datetime in seconds. Please see:

data _null_;
   x= '01JUN2017'd;
   y= '01Jun2017:10:12:25'dt;
   put 'X in days=' x ' and y in seconds=' y;
run;

So if your begindttm is a datetime on or near 1 Mar 2017 then it will not be between the date values.

Re: automatically find previous month and a year

tparvaiz — Wed, 26 Apr 2017 21:53:50 GMT

Thanks again everyone for the assistance