WHERE tran_month = &sysmonth
This snippet of code will return all transactions for the current month (all October dates regardless of year)
Is there a way to us the same &sysmonth except use the previous &sysmonth and current year only (in this case I want transactions from 9/1/21 through 9/30/21). I believe the current &sysmonth gives you all transaction in October regardless of the year. I only want the previous month for the current year.
data _null_;
call symputx('lastmonth',intnx('month',today(),-1,'b'));
run;
Macro variable &lastmonth now is the first day of the previous month. If you run the above code in October 2021, you get the first day of September 2021. Then you can search for any record with a date in the month that begins with &lastmonth.
Example in a DATA step
data want;
set have;
if intnx('month',date,0,'b')=&lastmonth;
run;
data _null_;
call symputx('lastmonth',intnx('month',today(),-1,'b'));
run;
Macro variable &lastmonth now is the first day of the previous month. If you run the above code in October 2021, you get the first day of September 2021. Then you can search for any record with a date in the month that begins with &lastmonth.
Example in a DATA step
data want;
set have;
if intnx('month',date,0,'b')=&lastmonth;
run;
Follow up question
tran_year1= YEAR(FEE_TRAN_DT) produces a 4 digit year (ie 2021)
tran_month1= MONTH(FEE_TRAN_DT)
In some cases it produces a single numeric value such as 8,9.4
I want to apply your solution using the
call symputx('lastmonth',put(intnx('month',today(),-1),yymmn6.))
I would need a way to add a leading zeros to the tran_month1= MONTH(FEE_TRAN_DT) then use a cat feature such as
catx('',tran_year1,tran_month1)
Is there a way to add the leading zero to the tran_month1 only if it produces a single digit month?
If you need a leading zero then that means:
1) you are comparing or using a character variable not date values, which isn't really obvious from your shown bits and likely is a cause of other problems and
2) Put a numeric value with the Z2. format to make a character value with leading zero if less than two digits: Tran_month1= put (MONTH(FEE_TRAN_DT), Z2.);
Don't split year and month and day. You are just making extra work for yourself.
Use actual SAS date values, SAS has already done the hard work to deal with calendar dates, so you don't have to.
Do it the way I showed in my answer above.
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.