Proc SQL - Get data in a dynamic rage of days.

[Tri_Luong]

Hi everyone,

I wanna use Proc SQL to create a new table in which I would like to get the observations with condition "WHERE" that REPORT_DATE between "first date of x+1 month(s) ago" and "2 days after last date of x month(s) ago". For eg.

If x = 1 --> data from 01/11/2018 to 03/01/2019.

Thank you so much for your help.

Tri Luong.

[RW9]

Please show test data in the form of a datastep:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

And then show what you want out from that test data.  Saying you want to code something very vague will not get you good answers.

At a guess, join on the dates having min()per group and max() per group joined by group, then you will have all the information on each row.

[ballardw]

Is this "x months ago" from the Report_date variable or some other variable or date? Where does the value for X come from, a variable, calculated or fixed in the code?

And possibly a critical question: Is report_date a SAS date value?

https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/... has a PDF with much information about dates.

[Tri_Luong]

Thank for your response,

About "X", I meant it is fixed value will be declared manually by user depending on their purpose in certain situation. 

Thanks.

[Tom · Accepted solution]

---

@Tri_Luong wrote:

Hi everyone,

I wanna use Proc SQL to create a new table in which I would like to get the observations with condition "WHERE" that REPORT_DATE between "first date of x+1 month(s) ago" and "2 days after last date of x month(s) ago". For eg.

If x = 1 --> data from 01/11/2018 to 03/01/2019.

Thank you so much for your help.

Tri Luong.

---

By AGO I assume you mean relative to the day that the program is run. You can use the DATE() function (alias TODAY()) to get the current date or "&sysdate9"d to get the date that the SAS session started.

To calculate a relative date use the INTNX() function.

Let's assume X is in macro variable.

REPORT_DATE between
intnx('month',"&sysdate9"d,-&x-1,'b')
and
intnx('month',"&sysdate9"d,-&x,'e')+2

[Tri_Luong]

It worked. Thank so much.