topic Re: How do I create this table using proc sql passthru using teradata calendar table in SAS Programming

How do I create this table using proc sql passthru using teradata calendar table

ilearnsas — Sun, 17 Nov 2019 02:08:26 GMT

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!

Re: How do I create this table using proc sql passthru using teradata calendar table

Patrick — Sun, 17 Nov 2019 04:19:23 GMT

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'

Re: How do I create this table using proc sql passthru using teradata calendar table

ilearnsas — Sun, 17 Nov 2019 13:23:24 GMT

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!

Re: How do I create this table using proc sql passthru using teradata calendar table

Patrick — Mon, 18 Nov 2019 03:16:18 GMT

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

Re: How do I create this table using proc sql passthru using teradata calendar table

Tom — Sun, 17 Nov 2019 22:19:40 GMT

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.