Date part for timestamp returing 0 in SAS

[SASUserRocks]

Dear Friends,

I have a timestamp value in excel of format MM/DD/YYYY HH:MM:SS AM/PM (Eg:- 5/9/2023 10:19:02 AM).

Need to derive date out of this field inorder to compare with dates provided in another  Calendar sheet. I use this Calendar sheet to derive week string and month for a particular date.

Calendar sheet :-

Month Week_Starts Week_Ends week_str
JAN2023 2023-01-01 2023-01-07 202301 wk1
JAN2023 2023-01-08 2023-01-14 202301 wk2
JAN2023 2023-01-15 2023-01-21 202301 wk3
JAN2023 2023-01-22 2023-01-31 202301 wk4
FEB2023 2023-02-01 2023-02-04 202302 wk1

Using datepart for timestamp value i get 0 and directly comparing timestamp wiht above week_Start and week_end results in missing values for few cases.

 Below is code :-

proc sql;
create table WBG.Workflow_ticket_data as select
ID,
put(ID,8.) as ID_char,
Created_Timestamp format ddmmyy10. ,
datepart(Created_Timestamp) ,
Channel,
Status,
Customer_CID,
Customer_Name,
Work_Item_No,
Service_Type,
Service_Sub_Type,
Queue_Name,
Svs_Failure,
Comments,
RM_Code,
RM_Name,
Creator,
Creator_Business,
Creator_Segment,
Creator_Team,
Last_Owner,
Last_Owner_Business,
Last_Owner_Segment,
Last_Owner_Team,
Last_Update_Timestamp,
Last_Parked_Timestamp,
Total_TAT,
Total_Parked_TAT ,
Total_TAT/1440 AS TAT,
'Staff Name'n,
Lead,
Manager,
Team,
Section,
DATA_CALENDAR.week_str,DATA_CALENDAR.Month
from Workflow_Ticket_data A left join WBG.StaffMapping B on upcase(A.Last_Owner)=upcase(B.'Staff ID'n)
left join WBG.DATA_CALENDAR
on datepart(Created_Timestamp) between DATA_CALENDAR.Week_Starts and DATA_CALENDAR.Week_Ends
WHERE A.Creator_Team IN('A','B');
run;

[ballardw]

Did you read the data from Excel into SAS? If so, run Proc Contents on the data set with that "timestamp" variable and share the result with us.

The Datepart function expects a SAS datetime value as input. I'm not sure that your have such in your data. To get a 0 value the datetime value of your timestamp the value would be between 01JAN1960:00:00:0 (number of seconds 0) and 01JAN1960:23:59:59 (86399 seconds unformatted). Which would point a likely problem with bringing the data into SAS.

You may need to share the Proc Contents information on your Week_starts and Week_ends values as well.

[SASUserRocks]

Proc contents on workflow ticket data table provide below

Created_Timestamp num lENGHT : 8 FORMAT : MMDDYY10.  INFORMAT: BLANK

wEEK STARTS :- YYMMDD10.

wEEK ENDS :- YYMMDD10.

[Kurt_Bremser]

So this points to created_timestamp being in fact a date, so the DATEPART function must not be used.

[ballardw]

---

@SASUserRocks wrote:

Proc contents on workflow ticket data table provide below

Created_Timestamp num lENGHT : 8 FORMAT : MMDDYY10.  INFORMAT: BLANK

wEEK STARTS :- YYMMDD10.

wEEK ENDS :- YYMMDD10.

---

So your timestamp variable is a date.

Remember above where I said if the value of the variable is between 0 and 86399? Since SAS dates are counts of days then 86399 corresponds to 20JUL2196. So applying the DATEPART function to any actual date value between 01JAN1960 and 20Jul2196 will result in a value of 0.

There is a small chance depending on how you brought the data from Excel that you have fractional days in your date value. Excel does datetime values by having the date as days and the hours, minutes and seconds as fraction of a day. SAS date formats will ignore the decimal portion for display. A brief example:

data example;
   x = 22650;
   y = 22650.4;
   put x= date9. y= date9.;
run;

[Kurt_Bremser]

Most probably it's in your data.

But it looks like you overcomplicate things. Use the WEEK function to derive week numbers from dates, you do not need a lookup table for this.

And look here:

Created_Timestamp format ddmmyy10. ,
datepart(Created_Timestamp) ,

A timestamp would be a count of seconds, which can't be displayed with a date format which expects a count of days. If it is a date, DATEPART will always return zero (date values lie in the range of seconds of the zero-day).

[SASUserRocks]

How i can use week function to derive week string. It derives in numerical.

[SASUserRocks]

Also week string is displayed in that manner to take it to Dashboard.

[Kurt_Bremser]

week_str = cats("wk",week(created_timestamp));

The CATS function does the conversion to character automatically and strips leading blanks from the arguments.

[SASUserRocks]

Hi Kurt

Thanks a lot for your response. I would like to get week number as WK1,WK2,WK3,WK4 for a month and cannot have like wk1,wk2...wk33 as it will confuse the dashboard users. Can you kindly suggest a solution to my original datepart of timestamp issue.

[ballardw]

---

@SASUserRocks wrote:

Hi Kurt

Thanks a lot for your response. I would like to get week number as WK1,WK2,WK3,WK4 for a month and cannot have like wk1,wk2...wk33 as it will confuse the dashboard users. Can you kindly suggest a solution to my original datepart of timestamp issue.

---

If you get a value like "wk1" and need "WK1" the use UPCASE function.

OR if you used

week_str = cats("wk",week(created_timestamp));

Change the "wk" to "WK" in the cats function call.

You should read the documentation of the WEEK function. There are three different results possible related to start day of week and how to treat the first week of year.

If you need  a week number within month then you may be looking for the INTCK function with the start of the month as the first date and the date of interest as the second to get the number of intervals within the month. But would have to see some actual values involved to see which values get assigned to which "week".

[Tom · Accepted solution]

---

@SASUserRocks wrote:

Hi Kurt

Thanks a lot for your response. I would like to get week number as WK1,WK2,WK3,WK4 for a month and cannot have like wk1,wk2...wk33 as it will confuse the dashboard users. Can you kindly suggest a solution to my original datepart of timestamp issue.

---

You need to define how you number weeks within a month.

If you just want the first 7 days to be week 1 and the next 7 to be week 2 then just use ARITHMETIC.

week_string = catx('WK',ceil(day(DATEVAR)/7));

Test it out to see if it works the way you want:

data test;
  length week_string $3 ;
  do datevar='01JAN2020'd to '29FEB2020'd ;
     week_string = catx('WK',ceil(day(DATEVAR)/7));
     output;
  end;
  formar datevar date9.;
run;

proc print;
run;