Errors: Variable has been defined as both character and numberic & Error 48-59 $COMMA was not found

[ChiRH]

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

[PaigeMiller]

ERROR: Variable Secno has been defined as both character and numeric.

You can't do a merge where the variable SECNO is numeric in one data set and Character in the other data set.

So you have to change one or the other so they are both numeric; or they are both character.

ERROR 48-59: The format $COMMA was not found or could not be loaded.

You can't assign the format COMMA to character variables.

[ChiRH]

Thanks for the response!

I've never gotten this error before and have been running this script for a couple of years now. Does that mean the formatting for when of the tables that the data is being pulled from has potentially been changed? 

[Tom]

---

@ChiRH wrote:

Thanks for the response!

I've never gotten this error before and have been running this script for a couple of years now. Does that mean the formatting for when of the tables that the data is being pulled from has potentially been changed? 

---

It is hard to read your log (the forum mangles the text if you don't use the Insert Code button) and it is very long, but if the source of the dataset is the ODBC connection, then YES.  Someone changed the definition of the variables in that remote database.

[PaigeMiller]

---

@ChiRH wrote:

Thanks for the response!

I've never gotten this error before and have been running this script for a couple of years now. Does that mean the formatting for when of the tables that the data is being pulled from has potentially been changed? 

---

This can happen if you are importing Excel files, and something in the Excel file has changed. For example, if a column in Excel is usually numeric, and in another Excel file it contains the letters NA in that numeric column, Excel is perfectly happy with that, but in SAS this column is now character, and thus the problem you are seeing.

[ballardw]

Most likely cause I can see:

438 proc import
439 datafile = "&file_dir\&last_filt_file"
440 out = prev_filt_output
441 replace;
442 getnames=yes;
443 run;

Proc import by default only examines a few rows of data before determining variable type, length and possibly informat.

Since this appears not to be a spreadsheet of similar file you may be able to use the option GUESSINGROWS=Max; as part of the Proc import code so that more rows are examined before setting properties. So you should get more consistent variable properties.

Better likely would be to replace proc import with a data step to control reading if the file layout doesn't change (or isn't supposed to change) and pretty much eliminate such inconsistencies.

[manikandanms]

You cant merge two variables with different data type. 

Solution:

1)  Change variables in to  type which is valid and then merge.

2) Check source data points where the data is being converted to NUM or CHAR and fix it.

3)if issue is with import from excel( common issue). check your excel and use import options to better read the data 

[Kurt_Bremser]

---

@manikandanms wrote:

You cant merge two variables with different formats. 

---

This is plain wrong. Different formats are no problem, different types are.