topic Re: Proc SQL - Get data in a dynamic rage of days. in SAS Programming

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

Tri_Luong — Mon, 28 Jan 2019 09:12:11 GMT

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.

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

RW9 — Mon, 28 Jan 2019 09:26:29 GMT

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-data-AKA-generate/ta-p/258712

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.

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

ballardw — Mon, 28 Jan 2019 15:41:02 GMT

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/ta-p/424354 has a PDF with much information about dates.

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

Tom — Mon, 28 Jan 2019 18:49:48 GMT

@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

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

Tri_Luong — Wed, 30 Jan 2019 03:21:42 GMT

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.

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

Tri_Luong — Wed, 30 Jan 2019 03:36:18 GMT

It worked. Thank so much.