Hello! Hope all is doing well. I just wanted to ask assistance in translating a SQL query into a SAS data step, thank you for your help.
SELECT DISTINCT TL.[LOCATION]
,TL.TELLER
,CASE WHEN CAST(TL.LOGIN_DATE AS date) BETWEEN '3/14/2021' AND '11/6/2021'
THEN CONVERT(VARCHAR(7),SWITCHOFFSET(TL.LOGIN_DATE AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'UTC',C.UTC_DST_OFFSET),120)
ELSE CONVERT(VARCHAR(7),SWITCHOFFSET(TL.LOGIN_DATE AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'UTC',C.UTC_OFFSET),120)
END AS YEAR_MONTH
,CASE WHEN CAST(TL.LOGIN_DATE AS date) BETWEEN '3/14/2021' AND '11/6/2021'
THEN FORMAT(SWITCHOFFSET(TL.LOGIN_DATE AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'UTC',C.UTC_DST_OFFSET),'ddd')
ELSE FORMAT(SWITCHOFFSET(TL.LOGIN_DATE AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'UTC',C.UTC_OFFSET),'ddd')
END AS DAY_OF_WEEK
,CASE WHEN CAST(TL.LOGIN_DATE AS date) BETWEEN '3/14/2021' AND '11/6/2021'
THEN FORMAT(SWITCHOFFSET(TL.LOGIN_DATE AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'UTC',C.UTC_DST_OFFSET),'h tt')
ELSE FORMAT(SWITCHOFFSET(TL.LOGIN_DATE AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'UTC',C.UTC_OFFSET),'h tt')
END AS LOCAL_TIME
FROM TellerLogin AS TL
JOIN TELLERID AS T
ON TL.TELLER = T.TELLER
JOIN Company AS C
ON TL.[LOCATION] = C.[LOCATION]
WHERE TL.[LOCATION] IS NOT NULL
AND TL.LOGIN_DATE BETWEEN '1/1/2021' AND '2/28/2022'
AND T.SECURITYGROUP_KEY IN (3, 4, 5, 7, 😎
AND C.IS_COLLECTIONS = 0
AND C.IS_INTERNET = 0
AND C.STORE_IS_OPEN = 1
... View more
