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
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
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?
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 |
@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.
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 - 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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.