Proc Sql Date logic

[Jyuen204]

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

[Kurt_Bremser]

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

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

[Reeza]

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

---

[PaigeMiller]

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)

[Jyuen204]

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

[Reeza]

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

---

[Jyuen204]

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

[Kurt_Bremser · Accepted solution]

Use

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

[Reeza]

Or just this:

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

[Kurt_Bremser]

---

@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.

[Jyuen204]

Thank you thank you!

[Kurt_Bremser]

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.