BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ilearnsas
Obsidian | Level 7

This is more of a teradata question since I am using proc sql passthru to generate the table below. 

 

I would like 12-29-2008 to be the start date for my table i.e. I want to assign this as the start date and with the week number corresponding to this as 209 (in a different column).

 

Then the next row follows with the following Monday (+7 days)  and the week number corresponding to this is being incremented by 1 (i.e.209+1) . The end date for this table is 12/27/2021 (Monday)

 

TABLE STRUCTURE is below:

Weekstart/Monday of the week    Week num

12/29/2008                                  209

1/5/2009                                       210

....

....

....

12/27/2021                                   887

 

 

I tried something like below but it only generated one row.

 

SEL WeekBegin , (WeekBegin - DATE '2008-12-29')/7 + 209 as week_num

FROM Sys_Calendar.BusinessCalendar

WHERE calendar_date = DATE '2008-12-29';

 

Could you please let me know how we can do an autocalculate using the syscalendar in teradata and obtain the subsequent rows i.e. the entire table all the way through 12/27/21 ? 

 

Thanks for your help on this! 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

If you select by WEEKBEGIN you are going to get 7 observations since there are 7 days in a week.

If you want to select by MONDAY then use 

day_of_week=2

Since that variable is code 1 to 7 with 1 meaning Sunday.

View solution in original post

4 REPLIES 4
Patrick
Opal | Level 21

Change your WHERE clause logic so that you select more than one row from the source table.

WHERE 
  calendar_date >= DATE '2008-12-29' 
  and calendar_date <= DATE '2021-12-27'
ilearnsas
Obsidian | Level 7

Thanks. I tried below.

    SEL WeekBegin, (WeekBegin - DATE '2008-12-29')/7 + 209 as week_num
FROM Sys_Calendar.BusinessCalendar 
    WHERE 
  calendar_date >= DATE '2008-12-29' 
  and calendar_date <= DATE '2021-12-27';
It gives 3 rows and 3 weeknum for the same date.

The output looked like below:

Weekbegin Weeknum
12/28/2008 209
12/28/2008 209
12/28/2008 209
...
12/26/2021 886
12/26/2021 886


The errors are below-
1. See duplicate entires (3 rows or 2 rows instead of 1)
2. Need to display monday of the week (however the last row displayed was 12/26/21 instead of 12/27/21 -- and the
corresponding week num should be 887 and not 886. Any suggestion on how we can fix this to get Monday of every week
until 12/27/21?



How do we get one unique row for each date and weeknum (no dups) and also get only Monday of every week?

Thanks!

 

Patrick
Opal | Level 21

I don't know why you're getting multiple rows for the query you've posted.

Updated post:

You're just selecting rows from a single "table" so there can't be any duplication of rows but it must already be this way in the source table. Your source table has a row per date so that's 7 rows per week - and that's what you get with your query. 

If you just need to de-dup the result then you can use DISTINCT in your Select statement or follow what @Tom suggests.

Tom
Super User Tom
Super User

If you select by WEEKBEGIN you are going to get 7 observations since there are 7 days in a week.

If you want to select by MONDAY then use 

day_of_week=2

Since that variable is code 1 to 7 with 1 meaning Sunday.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 963 views
  • 3 likes
  • 3 in conversation