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
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;
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;
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
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;
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
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;
Thanks again everyone for the assistance
@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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.