topic Re: Proc Sql Date logic in SAS Programming

Proc Sql Date logic

Jyuen204 — Fri, 27 Sep 2019 19:34:56 GMT

I have a table with a Date stanp formatted as 'MM-DD-YYYY' (5-23-2017 for example)

I want to use the PROC SQL to get the upcoming Saturday following this date. so that For May 21-26th, the date would be returned as 5-27-2017.

or Sept 22 - Sept 27 2019 would return as Sept 28 2019.

I've been trying different code variations but just cant get it. TIA

Re: Proc Sql Date logic

Kurt_Bremser — Fri, 27 Sep 2019 19:39:07 GMT

Since SAS considers Saturday the last day of the week, use this function:

intnx('week',your_date,0,'e')

Re: Proc Sql Date logic

Reeza — Fri, 27 Sep 2019 19:39:23 GMT

What is the type of your variable?

Assuming it's a SAS date, numeric with a date format, then you can use INTNX().

 intnx('week', date_variable, 0, 'e') as next_saturday

If you need to factor in holidays and such this will not work.

@Jyuen204 wrote:

I have a table with a Date stanp formatted as 'MM-DD-YYYY' (5-23-2017 for example)

I want to use the PROC SQL to get the upcoming Saturday following this date. so that For May 21-26th, the date would be returned as 5-27-2017.

or Sept 22 - Sept 27 2019 would return as Sept 28 2019.

I've been trying different code variations but just cant get it. TIA

Re: Proc Sql Date logic

PaigeMiller — Fri, 27 Sep 2019 19:39:35 GMT

Use WEEKDAY() to get the day of the week, then add the appropriate number of days to get to Saturday.

Example:

datevariable+7-weekday(datevariable)

Re: Proc Sql Date logic

Jyuen204 — Fri, 27 Sep 2019 19:54:22 GMT

my response date is formatted as such when I pull the data in SAS:

REPORT_DATE
28JAN2019

when I use the following syntax:
datepart(intnx('week',REPORT_DATE,0,'e')) format = date9. as END_DT

I end up with 01JAN1960 (starting point)
without the datepart i get a numeric 21582

Re: Proc Sql Date logic

Reeza — Fri, 27 Sep 2019 19:56:20 GMT

DATEPART() is applied on the original variable before you use INTNX(), but you're passing a datetime to INTNX with the interval set for a date variable.

Apply DATEPART() within INTNX() not after.

@Jyuen204 wrote:
my response date is formatted as such when I pull the data in SAS:

REPORT_DATE
28JAN2019

when I use the following syntax:
datepart(intnx('week',REPORT_DATE,0,'e')) format = date9. as END_DT

I end up with 01JAN1960 (starting point)
without the datepart i get a numeric 21582

Re: Proc Sql Date logic

Kurt_Bremser — Fri, 27 Sep 2019 20:02:11 GMT

@Jyuen204 wrote:
my response date is formatted as such when I pull the data in SAS:

REPORT_DATE
28JAN2019

when I use the following syntax:
datepart(intnx('week',REPORT_DATE,0,'e')) format = date9. as END_DT

I end up with 01JAN1960 (starting point)
without the datepart i get a numeric 21582

Why are you using datepart() on a date? datepart() is used to extract the date out of a datetime. 21582 is just the count of days from 1960-01-01, so this is correct. You just need to apply a date format to the new variable so it becomes human-readable.

I recommend that you study the chapter on dates and times in SAS Language Concepts.

Re: Proc Sql Date logic

Jyuen204 — Fri, 27 Sep 2019 20:02:57 GMT

datepart(REPORT_DATE) format = date9. as REPORT_DATE,
intnx('week',(datepart(REPORT_DATE) format = date9.),0,'e') as END_DT

i get a syntax error pulling this way.

I get the REPORT_DATE when converting to date9. fine

but using the intnx, i get a syntax error

Re: Proc Sql Date logic

Kurt_Bremser — Fri, 27 Sep 2019 20:14:06 GMT

Use

intnx('week',calculated REPORT_DATE,0,'e') format = date9. as END_DT

Re: Proc Sql Date logic

Jyuen204 — Fri, 27 Sep 2019 20:15:56 GMT

Thank you thank you!

Re: Proc Sql Date logic

Kurt_Bremser — Fri, 27 Sep 2019 20:22:53 GMT

If you had posted your SQL earlier, we would have arrived at this solution sooner; by only showing a short snippet of what you were actually doing you made it harder for us to understand the situation.

Always post whole steps, and complete logs of whole steps to enable rapid diagnosing. Supplying example data in usable form (data steps with datalines) is also essential in getting good answers quickly.

Re: Proc Sql Date logic

Reeza — Fri, 27 Sep 2019 20:35:19 GMT

Or just this:

intnx('week',datepart(REPORT_DATE),0,'e') as END_DT