Daylight Savings Time

[Eugenio211]

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

[PaigeMiller]

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.

You might want to consider explaining what this code does. That would make it easier for someone to help you.

Here is SAS code to find when daylight savings time begins and ends: https://support.sas.com/resources/papers/proceedings17/1047-2017.pdf

[ballardw]

You may want to consider reposting the code in a text box opened on this forum using the </> icon. If you read your original post at the 4th line from the bottom you may see that something was converted into an emoji instead of a value and a closing parentheses.

Do you have a SAS data set already? Are the "date" values actually SAS dates, i.e. variable type numeric with a date format associated such as date9 or (guess here) ddmmyy10?

[Eugenio211]

Hello my apology with regards to not being clear.

The format of our raw data date/time is '01JAN2021:01:34:00', what I wanted to do is that when the date and time is within the DST period then time is DST when outside DST then to local time. it's like doing case scenarios in the proc sql step.

we also have our own offset to DST, below is the data.  the data is also based on the province where the location is based.  

LOCATION	UTC_DST_OFFSET	UTC_OFFSET
1	-04:00	-05:00
2	-05:00	-06:00
3	4:39	4:39
4	4:39	4:39
5	4:39	4:39
6	-04:00	-05:00
7	4:39	4:39
8	-05:00	-06:00
9	-04:00	-05:00
10	-04:00	-05:00
11	-04:00	-05:00
12	-04:00	-05:00
13	-04:00	-05:00
14	-04:00	-05:00
15	-04:00	-05:00
16	-04:00	-05:00
17	-04:00	-05:00
18	-04:00	-05:00
19	-04:00	-05:00
20	-04:00	-05:00
21	-04:00	-05:00
22	-04:00	-05:00
23	-04:00	-05:00
24	-04:00	-05:00
25	-04:00	-05:00
26	-04:00	-05:00
27	-04:00	-05:00
28	-04:00	-05:00
29	-04:00	-05:00
30	-04:00	-05:00
31	-04:00	-05:00
32	-04:00	-05:00
33	-04:00	-05:00
34	-04:00	-05:00
35	-04:00	-05:00
36	-03:00	-04:00
37	-04:00	-05:00
38	-04:00	-05:00
39	-03:00	-04:00
40	-04:00	-05:00
41	-04:00	-05:00
42	-04:00	-05:00
43	-04:00	-05:00
44	-04:00	-05:00
45	-04:00	-05:00
46	-04:00	-05:00
47	-04:00	-05:00
48	-04:00	-05:00
49	-04:00	-05:00
50	-04:00	-05:00
51	-04:00	-05:00
52	-04:00	-05:00
53	-04:00	-05:00
54	-04:00	-05:00
55	-04:00	-05:00
56	-04:00	-05:00
57	-04:00	-05:00
58	-04:00	-05:00
59	-06:00	-07:00
60	-04:00	-05:00
61	-05:00	-06:00
62	-05:00	-06:00
63	-06:00	-07:00
64	-05:00	-06:00
65	-06:00	-07:00
66	-04:00	-05:00
67	-06:00	-07:00
68	-03:00	-04:00
69	-06:00	-07:00
70	-06:00	-07:00
71	-04:00	-05:00
72	-06:00	-07:00
73	-04:00	-05:00
74	-04:00	-05:00
75	-06:00	-07:00
76	-04:00	-05:00
77	-06:00	-07:00
78	-04:00	-05:00
79	-05:00	-06:00
80	-04:00	-05:00
81	-06:00	-07:00
82	-04:00	-05:00
83	-04:00	-05:00
84	-04:00	-05:00
85	-04:00	-05:00
86	-04:00	-05:00
87	-04:00	-05:00
88	-06:00	-07:00
89	-04:00	-05:00
90	-04:00	-05:00
91	-06:00	-07:00
92	-06:00	-07:00
93	-05:00	-06:00
94	-07:00	-08:00
95	-07:00	-08:00
96	-07:00	-08:00
97	-07:00	-08:00
98	-07:00	-08:00
99	-07:00	-08:00
100	-04:00	-05:00
101	-04:00	-05:00
102	-04:00	-05:00
103	-04:00	-05:00
104	-04:00	-05:00
105	-04:00	-05:00
106	-04:00	-05:00
107	-04:00	-05:00
108	-03:00	-04:00
109	-04:00	-05:00
110	-04:00	-05:00
111	-04:00	-05:00
112	-04:00	-05:00
113	-04:00	-05:00
114	-04:00	-05:00
115	-04:00	-05:00
116	-04:00	-05:00
117	-06:00	-07:00
118	-06:00	-07:00
119	-06:00	-07:00
120	-06:00	-07:00
121	-06:00	-07:00
122	-04:00	-05:00
123	-03:00	-04:00
124	-06:00	-07:00
125	-04:00	-05:00
126	-07:00	-08:00
127	-07:00	-08:00
128	-06:00	-07:00
129	-06:00	-07:00
130	-07:00	-08:00
131	-07:00	-08:00
132	-06:00	-07:00
133	-04:00	-05:00
134	-06:00	-07:00
135	-04:00	-05:00
136	-04:00	-05:00
137	-04:00	-05:00
138	-07:00	-08:00
139	-07:00	-08:00
140	-04:00	-05:00
141	-07:00	-08:00
142	-07:00	-08:00
143	-04:00	-05:00
144	-04:00	-05:00
145	-07:00	-08:00
146	-04:00	-05:00
147	-04:00	-05:00
148	-04:00	-05:00
149	-03:00	-04:00
150	-04:00	-05:00
151	-06:00	-06:00
152	-05:00	-06:00
153	-05:00	-06:00
154	-04:00	-05:00
155	-04:00	-05:00
156	-04:00	-05:00
157	-06:00	-07:00
158	-04:00	-05:00
159	-06:00	-07:00
160	-04:00	-05:00
161	-04:00	-05:00
162	-07:00	-08:00
163	-06:00	-06:00
164	-04:00	-05:00
165	-06:00	-07:00
166	-07:00	-08:00
167	-04:00	-05:00
168	-04:00	-05:00
169	-07:00	-08:00
170	-06:00	-06:00
171	-03:00	-04:00
172	-05:00	-06:00
173	-06:00	-07:00
174	-04:00	-05:00
175	-04:00	-05:00
176	-06:00	-06:00
177	-04:00	-05:00
178	-04:00	-05:00
179	-04:00	-05:00
180	-04:00	-05:00
181	-07:00	-08:00
182	-07:00	-08:00
183	-04:00	-05:00
184	-04:00	-05:00
185	-04:00	-05:00
186	-07:00	-08:00
187	-06:00	-06:00
188	-04:00	-05:00
189	-04:00	-05:00
190	-06:00	-07:00
191	-07:00	-08:00
192	-04:00	-05:00
193	-07:00	-08:00
194	-06:00	-06:00
195	-06:00	-07:00
196	-04:00	-05:00
197	-04:00	-05:00
198	-06:00	-07:00
199	-04:00	-05:00
200	-04:00	-05:00
201	-04:00	-05:00
202	-04:00	-05:00
203	-04:00	-05:00
204	-04:00	-05:00
205	-07:00	-08:00
206	-04:00	-05:00
207	-04:00	-05:00
208	-04:00	-05:00
209	-07:00	-08:00
210	-07:00	-08:00
211	-07:00	-08:00
212	-07:00	-08:00
213	-06:00	-06:00

[PaigeMiller]

---

@Eugenio211 wrote:

The format of our raw data date/time is '01JAN2021:01:34:00', what I wanted to do is that when the date and time is within the DST period then time is DST when outside DST then to local time.

---

I would imagine the code at the link I gave will get the job done.

[SASKiwi]

What's the reason for translating the SQL? You can easily run it as it is against the same database from SAS using SQL Passthru.

[Eugenio211]

Hello, I'm not familiar with SQL Passthru, how do you do that? thanks a lot.

[Eugenio211]

never mind I got it. Thanks a lot for your help.

[Eugenio211]

Hi @SASKiwi,
I've encountered the error below using SAS passthru, what do you mean by this? thanks.

ERROR: CLI open cursor error: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The timezone provided to builtin function
switchoffset is invalid.

[SASKiwi]

@Eugenio211 - OK, in that case that's an SQL Server problem and nothing to do with SAS. Perhaps you could search on SQL Server forums for a solution?