First error shows up after line 453. I never got this error until today, and I've been running the script for a couple of years now with no issues. There is another error after line 458.    Log is below for entire script (with error lines).   1 libname crspver odbc datasrc=crspind_verify schema=crspind; NOTE: Libref CRSPVER was successfully assigned as follows: Engine: ODBC Physical Name: crspind_verify 2 3 4 %let recon_dt = '25sep2019'd; /*effective date of most recent completed recon - after transitional recon - 4 ! usually a wednesday */ 5 %let ranking_dt = '09sep2019'd; /*effective date of the most recent completed ranking */ 6 %let freeze_dt = '29aug2019'd; /*the date the shares were frozen for the most recent ranking */ 7 %let next_freeze = '29nov2019'd; /*the date the shares will be frozen for the upcoming ranking */ 8 %let next_rank_dt = '09dec2019'd; /*effective date for the upcoming ranking */ 9 10 /* Values from the last recon date */ 11 data current_index; 12 set crspver.idxmbr; 13 keep secno begdt hld efffloatfctr; 14 where indno = 1002330 and enddt = &recon_dt; 15 run; NOTE: There were 3557 observations read from the data set CRSPVER.idxmbr. WHERE (indno=1002330) and (enddt='25SEP2019'D); NOTE: The data set WORK.CURRENT_INDEX has 3557 observations and 4 variables. NOTE: DATA statement used (Total process time): real time 0.53 seconds cpu time 0.09 seconds 16 17 /* Find corporate actions (secondary offering, merger) that fall on the recon date 18 and effect the index values - edited list 2/16/15 previously ('SEC','MEM','MEV')*/ 19 data CA_events1; 20 set crspver.CAEvent; 21 keep Secno CAType ExDt; 22 where ExDt = &recon_dt and CAType in ('SEC','MEM','SOM','SD','SS','RS','RDN')and enddt = .; 23 run; NOTE: There were 4 observations read from the data set CRSPVER.CAEvent. WHERE (ExDt='25SEP2019'D) and CAType in ('MEM', 'RDN', 'RS', 'SD', 'SEC', 'SOM', 'SS') and (enddt=.); NOTE: The data set WORK.CA_EVENTS1 has 4 observations and 3 variables. NOTE: DATA statement used (Total process time): real time 0.24 seconds cpu time 0.04 seconds 24 25 /* find corporate actions (secondary offering, merger) that fall between freeze date and ranking date 26 - edited list 2/16/15 previously ('SEC','MEM','MEV')*/ 27 28 data CA_events2; 29 set crspver.CAEvent; 30 keep Secno CAType ExDt; 31 where ExDt >= &freeze_dt and exdt < &ranking_dt and CAType in ('SEC','MEM','SOM','SD','SS','RS','RDN') and 31 ! enddt = .; 32 run; NOTE: There were 15 observations read from the data set CRSPVER.CAEvent. WHERE (ExDt>='29AUG2019'D and ExDt<'09SEP2019'D) and CAType in ('MEM', 'RDN', 'RS', 'SD', 'SEC', 'SOM', 'SS') and (enddt=.); NOTE: The data set WORK.CA_EVENTS2 has 15 observations and 3 variables. NOTE: DATA statement used (Total process time): real time 0.14 seconds cpu time 0.04 seconds 33 34 data CA_events; 35 set ca_events1 ca_events2; 36 run; NOTE: There were 4 observations read from the data set WORK.CA_EVENTS1. NOTE: There were 15 observations read from the data set WORK.CA_EVENTS2. NOTE: The data set WORK.CA_EVENTS has 19 observations and 3 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.03 seconds 37 38 proc sql; 39 create table CA_update as 40 select a.SecNo, b.begdt, b.hld, b.efffloatfctr 41 from CA_events a, 42 crspver.idxmbr b 43 where a.SecNo = b.SecNo 44 and a.ExDt = b.begdt 45 and b.IndNo = 1001330; NOTE: Table WORK.CA_UPDATE created, with 12 rows and 4 columns. 46 quit; NOTE: PROCEDURE SQL used (Total process time): real time 1.07 seconds cpu time 0.37 seconds 47 48 /* Catch any "truing-up" that has happened since the recon date */ 49 data index_changes; 50 set crspver.idxmbr; 51 keep secno begdt hld efffloatfctr; 52 where indno = 1001330 and begdt > &recon_dt and endvrsnid = .; 53 run; NOTE: There were 48 observations read from the data set CRSPVER.idxmbr. WHERE (indno=1001330) and (begdt>'25SEP2019'D) and (endvrsnid=.); NOTE: The data set WORK.INDEX_CHANGES has 48 observations and 4 variables. NOTE: DATA statement used (Total process time): real time 0.06 seconds cpu time 0.03 seconds 54 55 /* Apply updates to current_index */ 56 proc sort data=current_index; by secno; run; NOTE: There were 3557 observations read from the data set WORK.CURRENT_INDEX. NOTE: The data set WORK.CURRENT_INDEX has 3557 observations and 4 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 57 proc sort data=ca_update; by secno; run; NOTE: There were 12 observations read from the data set WORK.CA_UPDATE. NOTE: The data set WORK.CA_UPDATE has 12 observations and 4 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 58 data current_index; 59 update current_index 60 ca_update; 61 by secno; 62 run; NOTE: There were 3557 observations read from the data set WORK.CURRENT_INDEX. NOTE: There were 12 observations read from the data set WORK.CA_UPDATE. NOTE: The data set WORK.CURRENT_INDEX has 3558 observations and 4 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 63 64 proc sort data=index_changes; by secno; run; NOTE: There were 48 observations read from the data set WORK.INDEX_CHANGES. NOTE: The data set WORK.INDEX_CHANGES has 48 observations and 4 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 65 data current_index; 66 update current_index 67 index_changes; 68 by secno; 69 run; NOTE: There were 3558 observations read from the data set WORK.CURRENT_INDEX. NOTE: There were 48 observations read from the data set WORK.INDEX_CHANGES. NOTE: The data set WORK.CURRENT_INDEX has 3566 observations and 4 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 70 71 /* since we are now freezing shares prior to ranking we need to change the begdt to the freeze date if the begdt is 71 ! equal to ranking day */ 72 73 data current_index; 74 set current_index; 75 if begdt = &ranking_dt then begdt = &freeze_dt; 76 run; NOTE: There were 3566 observations read from the data set WORK.CURRENT_INDEX. NOTE: The data set WORK.CURRENT_INDEX has 3566 observations and 4 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.03 seconds 77 78 /* for those seurities not in the TM Index on ranking day we need to keep ranking day as the beg dt */ 79 80 proc sql; 81 create table Notin_atrank as 82 select a.begdt, a.secno, a.hld, a.efffloatfctr 83 from current_index a left join 84 crspver.idxmbrdly b 85 on a.SecNo = b.SecNo 86 and b.trddt = &ranking_dt 87 and b.IndNo = 1001330 88 where b.sodval is null; NOTE: Table WORK.NOTIN_ATRANK created, with 53 rows and 4 columns. 89 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.15 seconds cpu time 0.09 seconds 90 91 data notin_atrank; 92 set notin_atrank; 93 if begdt = &freeze_dt then begdt = &ranking_dt; 94 run; NOTE: There were 53 observations read from the data set WORK.NOTIN_ATRANK. NOTE: The data set WORK.NOTIN_ATRANK has 53 observations and 4 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 95 96 97 proc sort data= notin_atrank; by secno; run; NOTE: There were 53 observations read from the data set WORK.NOTIN_ATRANK. NOTE: The data set WORK.NOTIN_ATRANK has 53 observations and 4 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.00 seconds cpu time 0.01 seconds 98 data current_index; 99 update current_index 100 notin_atrank; 101 by secno; 102 run; NOTE: There were 3566 observations read from the data set WORK.CURRENT_INDEX. NOTE: There were 53 observations read from the data set WORK.NOTIN_ATRANK. NOTE: The data set WORK.CURRENT_INDEX has 3566 observations and 4 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 103 104 105 106 /* Get Universe TSO, FSO, and float factor on the date that current index used */ 107 proc sql; 108 create table index_shares as 109 select a.secno, b.tso, b.fso, b.floatfctr 110 from current_index a, 111 crspver.secmkt b 112 where a.secno = b.secno 113 and a.begdt = b.trddt 114 and b.endvrsnid = .; NOTE: Table WORK.INDEX_SHARES created, with 3566 rows and 4 columns. 115 quit; NOTE: PROCEDURE SQL used (Total process time): real time 56.05 seconds cpu time 25.17 seconds 116 117 /* Get the information from SecMkt that the new index will use. If running before the next freeze date use 117! maximum date available 118 if running after on or after freeze date and prior to next raning, then use the freeze date data but adjust 118! for any actionable CAs that 119 occur on or after freeze date*/ 120 121 proc sql; 122 create table max_trddt as 123 select max(trddt) as maxdt format YYMMDD10. 124 from crspver.secmkt; NOTE: Table WORK.MAX_TRDDT created, with 1 rows and 1 columns. 125 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.10 seconds cpu time 0.04 seconds 126 127 data max_trddt; 128 set max_trddt; 129 if maxdt > &next_freeze then maxdt = &next_freeze; 130 run; NOTE: There were 1 observations read from the data set WORK.MAX_TRDDT. NOTE: The data set WORK.MAX_TRDDT has 1 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 131 132 proc sql; 133 create table universe_shares as 134 select trddt, secno, tso, fso, floatfctr, sodprc 135 from crspver.secmkt a, 136 max_trddt b 137 where a.trddt = b.maxdt 138 and endvrsnid = .; NOTE: Table WORK.UNIVERSE_SHARES created, with 9183 rows and 6 columns. 139 quit; NOTE: PROCEDURE SQL used (Total process time): real time 1:16.66 cpu time 35.09 seconds 140 141 142 data CA_events3; 143 set crspver.CAEvent; 144 keep Secno ExDt; 145 where ExDt >= &next_freeze and exdt < &next_rank_dt and CAType in ('SEC','MEM','MEV') and enddt = .; 146 run; NOTE: There were 0 observations read from the data set CRSPVER.CAEvent. WHERE (ExDt>='29NOV2019'D and ExDt<'09DEC2019'D) and CAType in ('MEM', 'MEV', 'SEC') and (enddt=.); NOTE: The data set WORK.CA_EVENTS3 has 0 observations and 2 variables. NOTE: DATA statement used (Total process time): real time 0.18 seconds cpu time 0.04 seconds 147 148 proc sql; 149 create table universe_shares2 as 150 select a.trddt, a.secno, a.tso, a.fso, a.floatfctr, a.sodprc 151 from crspver.secmkt a, 152 ca_events3 b 153 154 where a.secno = b.secno and 155 a.trddt = b.exdt and 156 a.endvrsnid = .; NOTE: Table WORK.UNIVERSE_SHARES2 created, with 0 rows and 6 columns. 157 quit; NOTE: PROCEDURE SQL used (Total process time): real time 11.06 seconds cpu time 0.09 seconds 158 159 proc sort data= universe_shares; by secno; run; NOTE: There were 9183 observations read from the data set WORK.UNIVERSE_SHARES. NOTE: The data set WORK.UNIVERSE_SHARES has 9183 observations and 6 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 160 proc sort data= universe_shares2; by secno;run; NOTE: Input data set is empty. NOTE: The data set WORK.UNIVERSE_SHARES2 has 0 observations and 6 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 161 data universe_shares; 162 update universe_shares 163 universe_shares2; 164 by secno; 165 run; NOTE: There were 9183 observations read from the data set WORK.UNIVERSE_SHARES. NOTE: There were 0 observations read from the data set WORK.UNIVERSE_SHARES2. NOTE: The data set WORK.UNIVERSE_SHARES has 9183 observations and 6 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 166 167 168 169 170 171 /* Get stock ID info */ 172 data sec_id_info; 173 set crspver.secdesc; 174 keep secno cusip compname trdsymbol; 175 where endvrsnid = .; 176 run; NOTE: There were 9185 observations read from the data set CRSPVER.secdesc. WHERE endvrsnid=.; NOTE: The data set WORK.SEC_ID_INFO has 9185 observations and 4 variables. NOTE: DATA statement used (Total process time): real time 0.15 seconds cpu time 0.04 seconds 177 178 /* Get SODWgt */ 179 proc sql; 180 create table weights as 181 select a.indno, a.secno, a.sodwgt 182 from crspver.idxmbrdly a, 183 max_trddt b 184 where a.trddt = b.maxdt 185 and a.indno = 1001330 186 and a.endvrsnid = .; NOTE: Table WORK.WEIGHTS created, with 3543 rows and 3 columns. 187 quit; NOTE: PROCEDURE SQL used (Total process time): real time 22.58 seconds cpu time 9.40 seconds 188 189 /* Combine it all together and make our calculations */ 190 proc sort data=current_index; by secno; run; NOTE: There were 3566 observations read from the data set WORK.CURRENT_INDEX. NOTE: The data set WORK.CURRENT_INDEX has 3566 observations and 4 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 191 proc sort data=weights; by secno; run; NOTE: There were 3543 observations read from the data set WORK.WEIGHTS. NOTE: The data set WORK.WEIGHTS has 3543 observations and 3 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.03 seconds cpu time 0.03 seconds 192 proc sort data=index_shares; by secno; run; NOTE: There were 3566 observations read from the data set WORK.INDEX_SHARES. NOTE: The data set WORK.INDEX_SHARES has 3566 observations and 4 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 193 proc sort data=universe_shares; by secno; run; NOTE: There were 9183 observations read from the data set WORK.UNIVERSE_SHARES. NOTE: The data set WORK.UNIVERSE_SHARES has 9183 observations and 6 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.02 seconds cpu time 0.01 seconds 194 proc sort data=sec_id_info; by secno; run; NOTE: There were 9185 observations read from the data set WORK.SEC_ID_INFO. NOTE: The data set WORK.SEC_ID_INFO has 9185 observations and 4 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.04 seconds cpu time 0.04 seconds 195 data holdings_compare; 196 retain secno compname cusip trdsymbol universe_tso cur_idx_tso universe_fso cur_idx_fso 197 universe_ff cur_idx_ff universe_eff cur_idx_eff new_idx_hld cur_idx_hld 198 hld_pct_chg tso_chg float_chg SODWgt cur_price impact; 199 merge current_index (in=in_cur rename=(hld=cur_idx_hld efffloatfctr=cur_idx_eff)) 200 weights 201 index_shares (rename=(tso=cur_idx_tso fso=cur_idx_fso floatfctr=cur_idx_ff)) 202 universe_shares (rename=(tso=universe_tso fso=universe_fso floatfctr=universe_ff sodprc=cur_price)) 203 sec_id_info; 204 by secno; 205 keep secno compname cusip trdsymbol universe_tso cur_idx_tso universe_fso cur_idx_fso 206 universe_ff cur_idx_ff universe_eff cur_idx_eff new_idx_hld cur_idx_hld 207 hld_pct_chg tso_chg float_chg SODWgt cur_price impact; 208 209 if in_cur; 210 new_idx_hld = universe_tso * round(universe_ff, 5) / 100; 211 hld_pct_chg = (new_idx_hld - cur_idx_hld) / cur_idx_hld * 100; 212 tso_chg = (universe_tso - cur_idx_tso); 213 float_chg = (universe_fso - cur_idx_fso); 214 universe_eff = round(universe_ff, 5); 215 impact = abs(new_idx_hld - cur_idx_hld)*cur_price; 216 217 label cur_idx_hld = ' ' cur_idx_tso = ' ' cur_idx_fso = ' ' cur_idx_ff = ' ' cur_idx_eff = ' ' 218 universe_tso = ' ' universe_fso = ' ' universe_ff = ' '; 219 format universe_tso cur_idx_tso universe_fso cur_idx_fso new_idx_hld cur_idx_hld impact COMMA20. 220 universe_eff cur_idx_eff 3.0; 221 run; NOTE: Missing values were generated as a result of performing an operation on missing values. Each place is given by: (Number of times) at (Line):(Column). 22 at 210:32 22 at 210:34 22 at 211:32 22 at 212:29 22 at 213:31 22 at 214:20 22 at 215:14 22 at 215:30 22 at 215:44 NOTE: There were 3566 observations read from the data set WORK.CURRENT_INDEX. NOTE: There were 3543 observations read from the data set WORK.WEIGHTS. NOTE: There were 3566 observations read from the data set WORK.INDEX_SHARES. NOTE: There were 9183 observations read from the data set WORK.UNIVERSE_SHARES. NOTE: There were 9185 observations read from the data set WORK.SEC_ID_INFO. NOTE: The data set WORK.HOLDINGS_COMPARE has 3567 observations and 20 variables. NOTE: DATA statement used (Total process time): real time 0.06 seconds cpu time 0.07 seconds 222 223 /* change the percentage desired for checking here */ 224 225 data holdings_check; 226 set holdings_compare (rename=(secno=Secno 227 compname=Name 228 cusip=CUSIP 229 trdsymbol=Ticker 230 universe_tso=Universe_TSO 231 cur_idx_tso=Index_TSO 232 universe_fso=Universe_FSO 233 cur_idx_fso=Index_FSO 234 universe_ff=Universe_FF 235 cur_idx_ff=Index_FF 236 universe_eff=Universe_EFF 237 cur_idx_eff=Index_EFF 238 new_idx_hld=New_Holdings 239 cur_idx_hld=Index_Holdings 240 hld_pct_chg=Pct_Holdings_Change 241 tso_chg=TSO_Change 242 float_chg=FSO_Change 243 SODWgt=Weight 244 cur_price=Last_Price 245 impact=Index_Cap_Change)); 246 if abs(Pct_Holdings_Change) > 9; 247 run; NOTE: Missing values were generated as a result of performing an operation on missing values. Each place is given by: (Number of times) at (Line):(Column). 22 at 246:8 NOTE: There were 3567 observations read from the data set WORK.HOLDINGS_COMPARE. NOTE: The data set WORK.HOLDINGS_CHECK has 216 observations and 20 variables. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time 0.03 seconds 248 249 proc sort data = holdings_check; by desending Index_Cap_Change;run; NOTE: There were 216 observations read from the data set WORK.HOLDINGS_CHECK. NOTE: The data set WORK.HOLDINGS_CHECK has 216 observations and 20 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.00 seconds cpu time 0.01 seconds 250 251 252 /* WE NO LONGER NEED THE BLOOMBERG AND FACTSET FILES>>>>>COMMENTING THIS CODE OUT!!!! 253 /* Get current float data from Bloomberg and FactSet ----------------------------------------- */ 254 /* Import most recent Factset file ---------------------------------------------------------- 255 %let fs_path = \\10.110.18.204\Archive\Backoffice_Archive\IBStagingArea\FactSet; 256 257 /* Get list of all the available files (that we want) 258 data fs_files; 259 rc=filename("mydir","&fs_path"); 260 dsid = dopen("mydir"); 261 memcount = dnum(dsid); 262 do i = 1 to memcount; 263 fname = dread(dsid, i); 264 if find(fname,"FLOAT") > 0 then output; 265 end; 266 rc = dclose(dsid); 267 run; 268 269 /* Sort by name and get the last file 270 proc sort data=fs_files; by fname; run; 271 data _NULL_; 272 set fs_files end=last; 273 if last then do; 274 call symput("fs_file", trim(left(fname))); 275 end; 276 run; 277 278 %put &fs_file; 279 280 /* Import the file 281 data fs_import; 282 infile "&fs_path.\&fs_file" DSD DLM="|" FIRSTOBS=3 MISSOVER; 283 length date 4. float 7. float_pct_os 5.; 284 informat CUSIP $9. ticker $5. name $36. date YYMMDD10. float 10. float_pct_os 7.4; 285 format date YYMMDD10. float_pct_os 7.4; 286 drop ticker name; 287 input CUSIP $ ticker $ name $ date float float_pct_os; 288 if CUSIP = "TRAILER" then delete; 289 run; 290 291 /* Import most recent Bloomberg file ---------------------------------------------------------------- 292 %let bb_path=\\10.110.18.204\Archive\Backoffice_Archive\IBStagingArea\Bloomberg; 293 294 /* Get list of all the available files (that we want) 295 data bb_files; 296 rc=filename("mydir","&bb_path"); 297 dsid = dopen("mydir"); 298 memcount = dnum(dsid); 299 rx = prxparse("/equity_namr\.out_[0-9]+_[0-9]+F\.[0-9]+/"); 300 do i = 1 to memcount; 301 fname = dread(dsid, i); 302 if prxmatch(rx, fname) then output; 303 end; 304 rc = dclose(dsid); 305 run; 306 307 /* Sort by name and get the last file 308 proc sort data=bb_files; by fname; run; 309 data _NULL_; 310 set bb_files end=last; 311 if last then do; 312 call symput("bb_file", trim(left(fname))); 313 end; 314 run; 315 316 %put &bb_file; 317 318 /* Import the file 319 data bb_import; 320 infile "&bb_path.\&bb_file" FIRSTOBS=127 LRECL=1000 TRUNCOVER; 321 drop row floatc datec tsoc; 322 length CUSIP $9. ISIN $12.; 323 format date YYMMDD10. float_pct 8.4; 324 retain date; 325 input row $1000.; 326 327 if find(row, "|") > 0 then do; 328 ISIN = scan(row, 20, "|"); 329 if ISIN = "N.A." then ISIN = ""; 330 CUSIP = scan(row, 27, "|"); 331 if CUSIP = "N.A." then CUSIP = ""; 332 floatc = scan(row, 98, "|"); 333 if trim(left(floatc)) = "N.A." then float_pct = .; 334 else float_pct = input(floatc, 7.4); 335 tsoc = scan(row, 84, "|"); 336 if trim(left(tsoc)) = "N.A." then tso_real = .; 337 else tso_real = input(tsoc, 16.); 338 output; 339 end; 340 else do; 341 if find(row, "TIMESTARTED") > 0 then do; 342 datec = catt(scan(row, 3), scan(row, 2), scan(row, 6)); 343 date = input(datec, ANYDTDTE9.); 344 end; 345 end; 346 run; 347 348 /* Derive the CUSIP from the ISIN, where possible 349 %macro cusipchk(cusip_in,cusip_out); 350 s = 0; 351 do i = 1 to 8; 352 c = substr(&cusip_in.,i,1); 353 if (c >= '0' and c <= '9') then cval = input(c,1.); 354 else if (c >= 'A' and c <= 'Z') then cval = input(put(c,$hex2.),hex2.) - 65 + 10; 355 else s = .; 356 357 if s ^= . then do; 358 m = 2 - mod(i,2); 359 b = m*cval; 360 if (b >= 10) then s = s + floor(b/10) + mod(b,10); 361 else s = s + b; 362 end; 363 end; 364 365 if (s ^= .) then &cusip_out. = substr(&cusip_in.,1,8) || put( mod((10-mod(s,10)),10),1.); 366 else &cusip_out. = substr(&cusip_in.,1,8) || 'X'; /* missing check digit 367 %mend cusipchk; 368 369 data bb_import; 370 set bb_import; 371 drop I_CUSIP s i c cval m b I_CHECK; 372 where float_pct NE .; 373 374 /* If CUSIP is missing, try to derive it from ISIN 375 if CUSIP = '' and ISIN NE '' then do; 376 I_CUSIP = substr(ISIN, 3, 9); 377 %cusipchk(I_CUSIP, I_CHECK); 378 if I_CUSIP = I_CHECK then CUSIP = I_CUSIP; 379 end; 380 run; 381 382 /* Merge with percent change report ------------------------------------------------------------------------ 383 proc sort data=bb_import; by cusip; run; 384 proc sort nodupkey data=fs_import; by cusip; run; 385 data bbfs_float; 386 retain cusip bb_float fs_float bb_float_shares fs_float_shares; 387 merge bb_import (keep=cusip float_pct tso_real rename=(float_pct=bb_float)) 388 fs_import (keep=cusip float_pct_os float rename=(float=fs_float_shares float_pct_os=fs_float )); 389 by cusip; 390 keep cusip bb_float fs_float bb_float_shares fs_float_shares; 391 label bb_float = ' ' fs_float_shares = ' ' fs_float = ' '; 392 bb_float_shares = bb_float / 100 * tso_real; 393 run; 394 395 proc sort data=holdings_check; by cusip; run; 396 data hld_compare_float; 397 merge holdings_check (in=in_hc) 398 bbfs_float; 399 by cusip; 400 if in_hc; 401 format bb_float fs_float 4.3 402 bb_float_shares fs_float_shares COMMA20. 403 TrdSymbol $6.; 404 run; 405 406 /* Add hld values from previous run ----------------------------------------------------------------------- */ 407 /* Find previous file */ 408 409 410 %let file_dir = H:\CRSPMIOperationsDailyChecklist\Share Challenges\index holdings change report\Filter Output; 411 data prev_filter_runs; 412 rc=filename("mydir","&file_dir"); 413 dsid = dopen("mydir"); 414 memcount = dnum(dsid); 415 drop rc dsid memcount i; 416 format filt_dt YYMMDD10.; 417 do i = 1 to memcount; 418 fname = dread(dsid, i); 419 if find(fname,".xlsx") > 0 and substr(fname, 1, 23) = "Universe To Index Check" then do; 420 filt_dt_c = scan(scan(fname, 2, '_'), 1, '.'); 421 filt_dt = input(filt_dt_c, MMDDYY10.); 422 if filt_dt NE today() then output; 423 end; 424 end; 425 rc = dclose(dsid); 426 run; NOTE: The data set WORK.PREV_FILTER_RUNS has 188 observations and 3 variables. NOTE: DATA statement used (Total process time): real time 0.04 seconds cpu time 0.03 seconds 427 428 proc sort data=prev_filter_runs; by filt_dt; run; NOTE: There were 188 observations read from the data set WORK.PREV_FILTER_RUNS. NOTE: The data set WORK.PREV_FILTER_RUNS has 188 observations and 3 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 429 data _NULL_; 430 set prev_filter_runs end=last; 431 if last then do; 432 call symput("last_filt_file", trim(left(fname))); 433 end; 434 run; NOTE: There were 188 observations read from the data set WORK.PREV_FILTER_RUNS. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 435 436 %put Previous file = &file_dir\&last_filt_file; Previous file = H:\CRSPMIOperationsDailyChecklist\Share Challenges\index holdings change report\Filter Output\Universe To Index Check_10-30-2019.xlsx 437 438 proc import 439 datafile = "&file_dir\&last_filt_file" 440 out = prev_filt_output 441 replace; 442 getnames=yes; 443 run; NOTE: WORK.PREV_FILT_OUTPUT data set was successfully created. NOTE: The data set WORK.PREV_FILT_OUTPUT has 0 observations and 23 variables. NOTE: PROCEDURE IMPORT used (Total process time): real time 16.48 seconds cpu time 0.68 seconds 444 445 proc sort data=prev_filt_output; by secno; run; NOTE: Input data set is empty. NOTE: The data set WORK.PREV_FILT_OUTPUT has 0 observations and 23 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 446 proc sort data=holdings_check; by secno; run; NOTE: There were 216 observations read from the data set WORK.HOLDINGS_CHECK. NOTE: The data set WORK.HOLDINGS_CHECK has 216 observations and 20 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 447 proc contents data=prev_filt_output varnum;run; NOTE: Writing HTML Body file: sashtml.htm NOTE: PROCEDURE CONTENTS used (Total process time): real time 1.22 seconds cpu time 0.64 seconds 448 proc contents data=holdings_check varnum;run; NOTE: PROCEDURE CONTENTS used (Total process time): real time 0.02 seconds cpu time 0.03 seconds 449 450 data hld_compare_final; 451 retain Secno Name Ticker CUSIP Index_TSO Universe_TSO TSO_Change Index_FSO Universe_FSO FSO_Change Index_FF 451! Universe_FF Index_EFF Universe_EFF Index_Holdings New_Holdings Previous_Holdings Prev_Holdings_Diffs 451! Pct_Holdings_Change Change_Diffs Weight Last_Price Index_Cap_Change; 452 merge holdings_check (in=in_hcf) 453 prev_filt_output (keep=Secno New_Holdings rename=(New_Holdings=Previous_Holdings)); ERROR: Variable Secno has been defined as both character and numeric. 454 by Secno; 455 if in_hcf; 456 Prev_Holdings_Diffs = New_Holdings - Previous_Holdings; 457 Change_Diffs=(abs(TSO_Change) - abs(FSO_Change))/abs(TSO_Change); 458 format Secno COMMA20. Name $50. Ticker $6. CUSIP $10. Universe_TSO Index_TSO TSO_Change Universe_FSO 458! Index_FSO FSO_Change New_Holdings Index_Holdings Previous_Holdings Prev_Holdings_Diffs Weight Index_Cap_Change 458! COMMA20. -------- 48 ERROR 48-59: The format $COMMA was not found or could not be loaded. 459 Index_FF Universe_FF Universe_EFF Index_EFF Pct_Holdings_Change Last_Price Change_Diffs 3.2; 460 run; NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column). 456:42 NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.HLD_COMPARE_FINAL may be incomplete. When this step was stopped there were 0 observations and 23 variables. NOTE: DATA statement used (Total process time): real time 0.04 seconds cpu time 0.06 seconds 461 462 /*Export to Excel ----------------------------------------------------------------------------------------*/ 463 %let cur_dt = %sysfunc(putn(%sysfunc(today()), MMDDYYD10.)); 464 %put &cur_dt; 10-31-2019 465 466 proc sort data=hld_compare_final; by descending Index_Cap_Change; run; NOTE: Input data set is empty. NOTE: The data set WORK.HLD_COMPARE_FINAL has 0 observations and 23 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.00 seconds cpu time 0.01 seconds 467 proc export 468 data = hld_compare_final 469 outfile = "&file_dir\Universe To Index Check_&cur_dt..xlsx" 470 replace; 471 run; NOTE: "HLD_COMPARE_FINAL" range/sheet was successfully created. NOTE: PROCEDURE EXPORT used (Total process time): real time 0.72 seconds cpu time 0.43 seconds ... View more