BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tparvaiz
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
collinelliot
Barite | Level 11

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

9 REPLIES 9
collinelliot
Barite | Level 11

 

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;

tparvaiz
Obsidian | Level 7

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

 

 

collinelliot
Barite | Level 11

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;
tparvaiz
Obsidian | Level 7

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

collinelliot
Barite | Level 11

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;
tparvaiz
Obsidian | Level 7

Thanks again everyone for the assistance

ballardw
Super User

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

 

ballardw
Super User

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.

Astounding
PROC Star

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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