Hi There,
As a newbie SAS user, all I want to do is time series interpolation for missing values in my dataset, where is just one variable called XU100 in my dataset. I uploaded my Excel file to the SAS Studio, and the dates were accordingly arranged. However, when I gave a command, for example "putting zeros for missing values", it automatically added all the weekends and all the holidays perfectly excluded from the initial dataset and put zeros for them, as well. I introduced country-specific holidays to the system; however, I came across with some issues remained unsolved, particularly "duplicate time interval". There is an old post experienced a similar issue but it cannot be adapted to my dataset.
Your help will be appreciated. Thanks in advance.
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
68
69 /* Generated Code (IMPORT) */
70 /* Source File: sasdeneme2.xlsx */
71 /* Source Path: /home/u63366206/sasuser.v94/New Folder */
72 /* Code generated on: 1.04.2023 19:54 */
73
74 %web_drop_table(WORK.IMPORT);
NOTE: Table WORK.IMPORT has been dropped.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
memory 301.03k
OS Memory 24736.00k
Timestamp 02.04.2023 12:12:54 PM
Step Count 647 Switch Count 2
Page Faults 0
Page Reclaims 14
Page Swaps 0
Voluntary Context Switches 12
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0
75
76
77 FILENAME REFFILE '/home/u63366206/sasuser.v94/New Folder/sasdeneme2.xlsx';
78
79 PROC IMPORT DATAFILE=REFFILE
80 DBMS=XLSX
81 OUT=WORK.IMPORT;
82 GETNAMES=YES;
83 RUN;
NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with
options MSGLEVEL=I.
NOTE: The import data set has 545 observations and 2 variables.
NOTE: WORK.IMPORT data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.02 seconds
user cpu time 0.02 seconds
system cpu time 0.01 seconds
memory 3447.56k
OS Memory 27896.00k
Timestamp 02.04.2023 12:12:54 PM
Step Count 648 Switch Count 2
Page Faults 0
Page Reclaims 804
Page Swaps 0
Voluntary Context Switches 14
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 312
84
85 PROC CONTENTS DATA=WORK.IMPORT; RUN;
NOTE: PROCEDURE CONTENTS used (Total process time):
real time 0.03 seconds
user cpu time 0.03 seconds
system cpu time 0.00 seconds
memory 1850.21k
OS Memory 25768.00k
Timestamp 02.04.2023 12:12:54 PM
Step Count 649 Switch Count 0
Page Faults 0
Page Reclaims 138
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 8
86
87
88
89 data WORK.IMPORT2; set WORK.IMPORT;
90 * Convert XU100 from character to numeric;
91 XU1002 = input(XU100, best12.);
92 run;
NOTE: There were 545 observations read from the data set WORK.IMPORT.
NOTE: The data set WORK.IMPORT2 has 545 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 943.56k
OS Memory 26024.00k
Timestamp 02.04.2023 12:12:54 PM
Step Count 650 Switch Count 2
Page Faults 0
Page Reclaims 128
Page Swaps 0
Voluntary Context Switches 18
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 264
93
94 PROC CONTENTS DATA=WORK.IMPORT2; RUN;
NOTE: PROCEDURE CONTENTS used (Total process time):
real time 0.03 seconds
user cpu time 0.03 seconds
system cpu time 0.00 seconds
memory 978.15k
OS Memory 26024.00k
Timestamp 02.04.2023 12:12:54 PM
Step Count 651 Switch Count 0
Page Faults 0
Page Reclaims 98
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 24
95
96
97 /* Convert Date to SAS date */
98
99
100 proc timeseries data = WORK.IMPORT2
101 out = work.import3;
102 id date interval=weekday setmiss=0;
103 var XU1002;
104 run;
WARNING: 1 observations have been omitted before observation number 60 in data set WORK.IMPORT2 according to the INTERVAL=WEEKDAY
specification and the ID variable values. The current ID is Date=04/26/2021, the previous is Date=04/22/2021.
WARNING: 2 observations have been omitted before observation number 73 in data set WORK.IMPORT2 according to the INTERVAL=WEEKDAY
specification and the ID variable values. The current ID is Date=05/17/2021, the previous is Date=05/12/2021.
WARNING: 1 observations have been omitted before observation number 75 in data set WORK.IMPORT2 according to the INTERVAL=WEEKDAY
specification and the ID variable values. The current ID is Date=05/20/2021, the previous is Date=05/18/2021.
WARNING: 1 observations have been omitted before observation number 115 in data set WORK.IMPORT2 according to the INTERVAL=WEEKDAY
specification and the ID variable values. The current ID is Date=07/16/2021, the previous is Date=07/14/2021.
WARNING: 4 observations have been omitted before observation number 117 in data set WORK.IMPORT2 according to the INTERVAL=WEEKDAY
specification and the ID variable values. The current ID is Date=07/26/2021, the previous is Date=07/19/2021.
WARNING: 1 observations have been omitted before observation number 142 in data set WORK.IMPORT2 according to the INTERVAL=WEEKDAY
specification and the ID variable values. The current ID is Date=08/31/2021, the previous is Date=08/27/2021.
WARNING: 1 observations have been omitted before observation number 185 in data set WORK.IMPORT2 according to the INTERVAL=WEEKDAY
specification and the ID variable values. The current ID is Date=11/01/2021, the previous is Date=10/28/2021.
WARNING: 3 observations have been omitted before observation number 315 in data set WORK.IMPORT2 according to the INTERVAL=WEEKDAY
specification and the ID variable values. The current ID is Date=05/05/2022, the previous is Date=04/29/2022.
WARNING: 1 observations have been omitted before observation number 325 in data set WORK.IMPORT2 according to the INTERVAL=WEEKDAY
specification and the ID variable values. The current ID is Date=05/20/2022, the previous is Date=05/18/2022.
WARNING: 2 observations have been omitted before observation number 361 in data set WORK.IMPORT2 according to the INTERVAL=WEEKDAY
specification and the ID variable values. The current ID is Date=07/13/2022, the previous is Date=07/08/2022.
NOTE: Further warnings for gaps in data will not be printed.
NOTE: Number of series processed: 1
NOTE: There were 545 observations read from the data set WORK.IMPORT2.
NOTE: The data set WORK.IMPORT3 has 564 observations and 2 variables.
NOTE: PROCEDURE TIMESERIES used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 826.87k
OS Memory 26024.00k
Timestamp 02.04.2023 12:12:54 PM
Step Count 652 Switch Count 2
Page Faults 0
Page Reclaims 121
Page Swaps 0
Voluntary Context Switches 14
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 272
105
106
107
108 /* HOLIDAYS */
109 data holidays ;
110 length HolidayName $ 30 ;
111 array WkDayShift [7] _temporary_ ( 1 5*0 -1 ) ;
112 retain ShiftToggle /* 0 */ 1 ;
113 do Year = 2021 to 2023 ;
114
115 HolidayName = "New Year's Day" ;
116 * 1 Jan;
117 HoliDate = holiday('newyear', year) ;
118 dow = weekday(HoliDate) ;
119 HoliDate = intnx( 'day', HoliDate, WkDayShift[dow] * ShiftToggle ) ;
120 if not ( (dow EQ 7) and ShiftToggle ) then output ;
121
122 HolidayName = "Children's Day";
123 * 23 Apr ;
124 HoliDate = mdy(4, 23, year) ;
125 output ;
126
127 HolidayName = "Sport Fest";
128 * 19 May ;
129 HoliDate = mdy(5, 19, year) ;
130 output ;
131
132 HolidayName = "Turkish Independence Day";
133 * 29 Oct ;
134 HoliDate = mdy(10, 29, year) ;
135 output ;
136
137 HolidayName = "Turkish Labor Day";
138 * 1 May ;
139 HoliDate = mdy(5, 1, year) ;
140 output ;
141
142 HolidayName = "Turkish Victory Day";
143 * 30 Aug ;
144 HoliDate = mdy(8, 30, year) ;
145 output ;
146
147 HolidayName = "Democracy and National Unity Day";
148 * 15 Jul ;
149 HoliDate = mdy(7, 15, year) ;
150 output ;
151
152 HolidayName = "Ramadan20211";
153 * 13 May in 2021- ;
154 HoliDate = mdy(5, 13, year) ;
155 if year EQ 2021 then output ;
156
157 HolidayName = "Ramadan20212";
158 * 14 May in 2021- ;
159 HoliDate = mdy(5, 14, year) ;
160 if year EQ 2021 then output ;
161
162 HolidayName = "Ramadan20221";
163 * 2 May in 2022- ;
164 HoliDate = mdy(5, 2, year) ;
165 if year EQ 2022 then output ;
166
167 HolidayName = "Ramadan20222";
168 * 3 May in 2022- ;
169 HoliDate = mdy(5, 3, year) ;
170 if year EQ 2022 then output ;
171
172 HolidayName = "Ramadan20223";
173 * 4 May in 2022- ;
174 HoliDate = mdy(5, 4, year) ;
175 if year EQ 2022 then output ;
176
177 HolidayName = "Kurban20211";
178 * 20 Jul in 2021- ;
179 HoliDate = mdy(7, 20, year) ;
180 if year EQ 2021 then output ;
181
182 HolidayName = "Kurban20212";
183 * 21 Jul in 2021- ;
184 HoliDate = mdy(7, 21, year) ;
185 if year EQ 2021 then output ;
186
187 HolidayName = "Kurban20213";
188 * 22 Jul in 2021- ;
189 HoliDate = mdy(7, 22, year) ;
190 if year EQ 2021 then output ;
191
192 HolidayName = "Kurban20214";
193 * 23 Jul in 2021- ;
194 HoliDate = mdy(7, 23, year) ;
195 if year EQ 2021 then output ;
196
197 HolidayName = "Kurban20223";
198 * 11 Jul in 2022- ;
199 HoliDate = mdy(7, 11, year) ;
200 if year EQ 2022 then output ;
201
202 HolidayName = "Kurban20224";
203 * 12 Jul in 2022- ;
204 HoliDate = mdy(7, 12, year) ;
205 if year EQ 2022 then output ;
206
207 end ;
208
209 keep year HolidayName HoliDate ;
210 format HoliDate weekdate17. ;
211 run ;
NOTE: The data set WORK.HOLIDAYS has 31 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
memory 867.28k
OS Memory 25764.00k
Timestamp 02.04.2023 12:12:54 PM
Step Count 653 Switch Count 2
Page Faults 0
Page Reclaims 114
Page Swaps 0
Voluntary Context Switches 15
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 272
212
213 proc sort data=holidays;
214 by holidate;
215 run;
NOTE: There were 31 observations read from the data set WORK.HOLIDAYS.
NOTE: The data set WORK.HOLIDAYS has 31 observations and 3 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 1040.84k
OS Memory 26024.00k
Timestamp 02.04.2023 12:12:54 PM
Step Count 654 Switch Count 2
Page Faults 0
Page Reclaims 118
Page Swaps 0
Voluntary Context Switches 12
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 264
216
217 proc print data=holidays;
218 run;
NOTE: There were 31 observations read from the data set WORK.HOLIDAYS.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.02 seconds
user cpu time 0.03 seconds
system cpu time 0.00 seconds
memory 459.50k
OS Memory 25764.00k
Timestamp 02.04.2023 12:12:54 PM
Step Count 655 Switch Count 0
Page Faults 0
Page Reclaims 63
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 16
219
220
221
222 /* HOLIDAY EXCLUSION */
223
224 data Mon_Fry ;
225 do MTuWThF = '01Feb2021'd to '31Mar2023'd ;
226 if weekday(MTuWThF) in (2:6) then output ;
227 end ;
228 format MTuWThF weekdate17. ;
229 run ;
NOTE: The data set WORK.MON_FRY has 565 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 662.68k
OS Memory 25764.00k
Timestamp 02.04.2023 12:12:54 PM
Step Count 656 Switch Count 2
Page Faults 0
Page Reclaims 90
Page Swaps 0
Voluntary Context Switches 17
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 264
230
231
232 data active_days(keep=begin) ;
233 merge Mon_Fry (rename=(MTuWThF =Begin) in=weekday )
234 holidays (rename=(holidate =Begin) in=holiday ) ;
235 if weekday and not (holiday) ;
236 by begin ;
237 run;
NOTE: There were 565 observations read from the data set WORK.MON_FRY.
NOTE: There were 31 observations read from the data set WORK.HOLIDAYS.
NOTE: The data set WORK.ACTIVE_DAYS has 545 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 1441.65k
OS Memory 26284.00k
Timestamp 02.04.2023 12:12:54 PM
Step Count 657 Switch Count 2
Page Faults 0
Page Reclaims 172
Page Swaps 0
Voluntary Context Switches 9
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 264
238
239 options intervalds=(BusinessDay=active_days) ;
240 run ;
241
242
243 proc timeseries data = WORK.IMPORT3
244 out = work.import4;
245 id date interval=businessday setmiss=0;
246 var XU1002;
247 run;
ERROR: Duplicate time interval found at observation number 60 in the data set WORK.IMPORT3, according to the INTERVAL=BUSINESSDAY
option and the ID variable values. The current ID is Date=23-APR-2021 and the previous is Date=22-APR-2021, which are within
the same BUSINESSDAY interval.
Check that INTERVAL=BUSINESSDAY is correct for this data set, and that the ID variable Date contains SAS date or datetime
values that correctly identify the observations.
NOTE: Execution is aborted because of invalid ID values or invalid INTERVAL= option.
NOTE: Number of series processed: 0
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 60 observations read from the data set WORK.IMPORT3.
WARNING: The data set WORK.IMPORT4 may be incomplete. When this step was stopped there were 0 observations and 2 variables.
WARNING: Data set WORK.IMPORT4 was not replaced because this step was stopped.
NOTE: PROCEDURE TIMESERIES used (Total process time):
real time 0.00 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
memory 1047.28k
OS Memory 26280.00k
Timestamp 02.04.2023 12:12:54 PM
Step Count 658 Switch Count 0
Page Faults 0
Page Reclaims 238
Page Swaps 0
Voluntary Context Switches 1
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 16
248
249 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
259
How to Concatenate Values
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.
Click image to register for webinar
Classroom Training Available!
Select SAS Training centers are offering in-person courses. View upcoming courses for:
-
486 views
-
0 likes
-
-
