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

Hi all,

My requirement is to use a filter code to select only current month's data from given table and condition I need to select is where report _date >= first_day_of_month and report_date <= last_day_of_month

My data looks like this

Account_id        Report_date

1                           12JUN2013

2                            17JAN2014

3                            19MAY2014

The code that I need to modify (I wrote this )

%let month_key=201405;

Proc Sql;

Create table work.want as

Select * from Work.have

Where report _date >= intnx ('month', &month_key,0) and report_date<=intnx('month',&month_key,0)-1;

Quit;

The value of month_key will be passed like this only. Don't know where I am going wrong but it's producing wrong output.

Need your help on this. 

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

tested:

Proc Sql;

Create table work.want as

Select * from Work.have

Where report_date between intnx('month',%sysfunc(inputn(&month_key,yymmn6.)),0,'b') and intnx('month',%sysfunc(inputn(&month_key,yymmn6.)),0,'e');

Quit;

or

Proc Sql;

Create table work.want2 as

Select * from Work.have

Where report_date between intnx('month',input("&month_key",yymmn6.),0,'b') and intnx('month',input("&month_key",yymmn6.),0,'e');

Quit;

View solution in original post

3 REPLIES 3
Linlin
Lapis Lazuli | Level 10


data have;
input Account_id        Report_date : date9.;
format report_date date9.;
cards;
1                           12JUN2013
2                            17JAN2014
3                            19MAY2014

%let month_key=201405;

Proc Sql;
Create table work.want as
Select * from Work.have
Where year(report_date)=%substr(&month_key,1,4) and month(report_date)=%substr(&month_key,5);
Quit;

Quentin
Super User

Hi,

INTNX wants a SAS Date, and 201405 is not a SAS date (at least it's not the SAS date you want).

You can use INPUT() to convert 201405 to a SAS date, something like: (untested)

%put %sysfunc(inputn(201405,yymmn6.),mmddyy10.); 
%put %sysfunc(intnx (month,%sysfunc(inputn(201405,yymmn6.)) ,0,B),mmddyy10.);
%put %sysfunc(intnx (month,%sysfunc(inputn(201405,yymmn6.)) ,0,E),mmddyy10.);

Where
  report _date     ge %sysfunc(intnx (month,%sysfunc(inputn(201405,yymmn6.)) ,0,B))
  and report_date le %sysfunc(intnx (month,%sysfunc(inputn(201405,yymmn6.)) ,0,E))
;

Actually you probably dont need the %sysfunc, and could instead just replace &month_key in your approach with inputn(&monthkey,yymmn6.)  .  But sometimes the macro language cam makes it easier to test/debug (not sure I've ever said *that* before...

HTH,

-Q.

The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.
Linlin
Lapis Lazuli | Level 10

tested:

Proc Sql;

Create table work.want as

Select * from Work.have

Where report_date between intnx('month',%sysfunc(inputn(&month_key,yymmn6.)),0,'b') and intnx('month',%sysfunc(inputn(&month_key,yymmn6.)),0,'e');

Quit;

or

Proc Sql;

Create table work.want2 as

Select * from Work.have

Where report_date between intnx('month',input("&month_key",yymmn6.),0,'b') and intnx('month',input("&month_key",yymmn6.),0,'e');

Quit;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 10649 views
  • 1 like
  • 3 in conversation