Assigning A "Week Start Date"

[Robert Walker]

Hi Listers,

I have a file of product sales by brand with daily transaction dates. The dates are in format "1-Jan-2016", etc.

I need to aggregate the file, such that each date is assigned to a "week start date". The "week start date" would begin on a Sunday, and include all dates through the following Saturday. This presents some issues, in that "1-Jan-2016" should be classified as being a date capture during the week of Sunday, 12-27-2015.

While I can extract week numbers, month, and years, I sense that I need to create a loop to find the closest Sunday and then assign that "week start date".

Unless there is a better approach I am missing?

Thanks,

Bob Walker
www.safllc.com

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Rich Ulrich]

No loop: compute it directly.  Refer to the date functions in order to ...

Compute #weeks =    trunc( (days from earliest_Sunday to current date)/7 )
  Add to earliest_Sunday the number of days in  (7* #weeks )  to get the Start_Sunday.

That's it, barring carelessness.

--
Rich Ulrich

> Date: Tue, 12 Jul 2016 23:26:17 +0000

> From: [hidden email]
> Subject: Assigning A "Week Start Date"
> To: [hidden email]
>
> Hi Listers,
>
> I have a file of product sales by brand with daily transaction dates. The dates are in format "1-Jan-2016", etc.
>
> I need to aggregate the file, such that each date is assigned to a "week start date". The "week start date" would begin on a Sunday, and include all dates through the following Saturday. This presents some issues, in that "1-Jan-2016" should be classified as being a date capture during the week of Sunday, 12-27-2015.
>
> While I can extract week numbers, month, and years, I sense that I need to create a loop to find the closest Sunday and then assign that "week start date".
>
> Unless there is a better approach I am missing?
>
> Thanks,
>

===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

[Bruce Weaver]

I don't have SPSS on this computer, so cannot test, but does this work?

COMPUTE #DayOfWeek = XDATE.WKDAY(DateVar).
COMPUTE StartDate = DateVar - (#DayOfWeek-1)*60*60*24.
FORMATS StartDate(DATE11).

Explanation:

When #DayOfWeek = 1 (Sunday), StartDate = DateVar.
When #DayOfWeek = 2 (Monday), StartDate = DateVar - # of seconds in 1 day.
When #DayOfWeek = 3 (Tuesday), StartDate = DateVar - # of seconds in 2 days.
Etc.
When #DayOfWeek = 7 (Saturday), StartDate = DateVar - # of seconds in 6 days.

Bob Walker wrote

Hi Listers,

I have a file of product sales by brand with daily transaction dates. The dates are in format "1-Jan-2016", etc.

I need to aggregate the file, such that each date is assigned to a "week start date". The "week start date" would begin on a Sunday, and include all dates through the following Saturday. This presents some issues, in that "1-Jan-2016" should be classified as being a date capture during the week of Sunday, 12-27-2015.

While I can extract week numbers, month, and years, I sense that I need to create a loop to find the closest Sunday and then assign that "week start date".

Unless there is a better approach I am missing?

Thanks,

Bob Walker
www.safllc.com

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Robert Walker]

Thanks Rich,

 

Can't quite figure out how to get the earliest Sunday as I have a database that spans 2012-2016 but will experiment with your code!

 

Bob Walker

www.safllc.com

 

From: Rich Ulrich [mailto:[hidden email]]
Sent: Tuesday, July 12, 2016 8:28 PM
To: Bob Walker <[hidden email]>; SPSS list <[hidden email]>
Subject: RE: Assigning A "Week Start Date"

 

No loop: compute it directly.  Refer to the date functions in order to ...

Compute #weeks =    trunc( (days from earliest_Sunday to current date)/7 )
  Add to earliest_Sunday the number of days in  (7* #weeks )  to get the Start_Sunday.

That's it, barring carelessness.

--
Rich Ulrich

> Date: Tue, 12 Jul 2016 23:26:17 +0000
> From: [hidden email]
> Subject: Assigning A "Week Start Date"
> To: [hidden email]
>
> Hi Listers,
>
> I have a file of product sales by brand with daily transaction dates. The dates are in format "1-Jan-2016", etc.
>
> I need to aggregate the file, such that each date is assigned to a "week start date". The "week start date" would begin on a Sunday, and include all dates through the following Saturday. This presents some issues, in that "1-Jan-2016" should be classified as being a date capture during the week of Sunday, 12-27-2015.
>
> While I can extract week numbers, month, and years, I sense that I need to create a loop to find the closest Sunday and then assign that "week start date".
>
> Unless there is a better approach I am missing?
>
> Thanks,
>

===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

[Robert Walker]

Bruce,

Nicely done - yes, works as intended. I have to remember that everything in dates is based on seconds.

Thanks very much,

Bob Walker
www.safllc.com
 

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Bruce Weaver
Sent: Tuesday, July 12, 2016 10:01 PM
To: [hidden email]
Subject: Re: Assigning A "Week Start Date"

I don't have SPSS on this computer, so cannot test, but does this work?

COMPUTE #DayOfWeek = XDATE.WKDAY(DateVar).
COMPUTE StartDate = DateVar - (#DayOfWeek-1)*60*60*24.
FORMATS StartDate(DATE11).

Explanation:

When #DayOfWeek = 1 (Sunday), StartDate = DateVar.
When #DayOfWeek = 2 (Monday), StartDate = DateVar - # of seconds in 1 day.
When #DayOfWeek = 3 (Tuesday), StartDate = DateVar - # of seconds in 2 days.
Etc.
When #DayOfWeek = 7 (Saturday), StartDate = DateVar - # of seconds in 6 days.



Bob Walker wrote

> Hi Listers,
>
> I have a file of product sales by brand with daily transaction dates.
> The dates are in format "1-Jan-2016", etc.
>
> I need to aggregate the file, such that each date is assigned to a
> "week start date". The "week start date" would begin on a Sunday, and
> include all dates through the following Saturday. This presents some
> issues, in that "1-Jan-2016" should be classified as being a date
> capture during the week of Sunday, 12-27-2015.
>
> While I can extract week numbers, month, and years, I sense that I
> need to create a loop to find the closest Sunday and then assign that
> "week start date".
>
> Unless there is a better approach I am missing?
>
> Thanks,
>
> Bob Walker
> www.safllc.com
>
> =====================
> To manage your subscription to SPSSX-L, send a message to

> LISTSERV@.UGA

>  (not to SPSSX-L), with no body text except the command. To leave the
> list, send the command SIGNOFF SPSSX-L For a list of commands to
> manage subscriptions, send the command INFO REFCARD





-----
--
Bruce Weaver
[hidden email]
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Assigning-A-Week-Start-Date-tp5732700p5732708.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD