DATA Step, Macro, Functions and more

automatically find previous month and a year

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 129
Accepted Solution

automatically find previous month and a year

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

 


Accepted Solutions
Solution
‎04-26-2017 05:53 PM
PROC Star
Posts: 288

Re: automatically find previous month and a year

[ Edited ]

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;

View solution in original post


All Replies
PROC Star
Posts: 288

Re: automatically find previous month and a year

 

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;

Frequent Contributor
Posts: 129

Re: automatically find previous month and a year

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

 

 

PROC Star
Posts: 288

Re: automatically find previous month and a year

[ Edited ]

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;
Frequent Contributor
Posts: 129

Re: automatically find previous month and a year

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

Solution
‎04-26-2017 05:53 PM
PROC Star
Posts: 288

Re: automatically find previous month and a year

[ Edited ]

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;
Frequent Contributor
Posts: 129

Re: automatically find previous month and a year

Thanks again everyone for the assistance

Super User
Posts: 10,466

Re: automatically find previous month and a year


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.

 

Super User
Posts: 10,466

Re: automatically find previous month and a year

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.

Super User
Posts: 5,071

Re: automatically find previous month and a year

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. 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 275 views
  • 1 like
  • 4 in conversation