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;
... View more