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
The first thing I would do is to look at data set ACTIVE_DAYS with your own eyes and make sure it contains the correct days.
Yes, ACTIVE_DAYS are exactly the dates that I want to include within my study.
What is SETMISS=0 doing?
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.