Thank you Jim and Reeza!! Thank you very much! ... View more

So to call Macro, I should add this line first: %loop(year, nfirms), is that right? ... View more

Hi Jim, Thank you so much for the help! And thank you for your patience with a new user. Here is all logs I have in log window. The code I got is from website posted by others. I guess it worked with others.  306 data QDC.temp1;set temp1;run; NOTE: There were 236480 observations read from the data set WORK.TEMP1. NOTE: The data set QDC.TEMP1 has 236480 observations and 12 variables. NOTE: DATA statement used (Total process time): real time 0.66 seconds cpu time 0.04 seconds 307 308 proc sort data=temp1; by year;run; NOTE: Input data set is already sorted, no sorting done. NOTE: PROCEDURE SORT used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 309 310 %macro loop(year, nfirms); 311 OPTIONS MCOMPILENOTE=NOAUTOCALL; 312 OPTIONS SYMBOLGEN; 313 OPTIONS MPRINT; 314 /* Loop by firm &i for year &year. This will generate a dataset of 314! firm i-j AcctComp for each year. */ 315 316 data temp1a; set temp1; if year(datadate1)=&year; 317 proc sort; by gvkey1; 318 319 data nfirms; set temp1a; 320 keep gvkey1; 321 proc sort nodupkey; by gvkey1; 322 323 data nfirms; set nfirms; id = _N_; 324 325 data temp1a; merge temp1a nfirms; by gvkey1; 326 327 %do i= 1 %to &nfirms; 328 329 data est1; set temp1a; 330 proc sort nodupkey; by gvkey1; 331 332 data est2; set temp1a; if id = &i; 333 334 PROC SQL; 335 CREATE TABLE est2 AS SELECT 336 est2.*, est1.gvkey1 AS gvkey_j, est1.a_i AS a_j, est1.b_i AS b_j 337 FROM est2 LEFT JOIN est1 338 ON est2.gvkey1 ~= est1.gvkey1 339 AND est2.sic2 = est1.sic2; 340 QUIT; 341 342 data est2; set est2; 343 a_dif = a_i - a_j; 344 b_dif = b_i - b_j; 345 drop a_i a_j b_i b_j; 346 347 proc sort; by gvkey1 datadate1 fqenddt1 datacqtr1 gvkey_j; 348 349 data est2; set est2; 350 351 proc sort; by gvkey1 datadate1 fqenddt1 datacqtr1 gvkey_j fqenddt; 352 353 data est2; set est2; 354 error = ABS(a_dif + bhr * b_dif); 355 356 proc sort; by gvkey1 datadate1 gvkey_j fqenddt; 357 358 PROC UNIVARIATE NOPRINT; by gvkey1 datadate1 gvkey_j; 359 VAR error ; 360 OUTPUT OUT = univ N=n MEAN = me_error ; 361 362 data univ; set univ; 363 acctcomp = -1 * me_error*100; 364 acctcomp = round(acctcomp, 0.001); 365 366 rename gvkey1 = gvkey_i; 367 rename datadate1 = datadate_i; 368 369 if not missing (acctcomp); 370 if 14 <= n <= 16; 371 372 drop me_error n; 373 374 PROC APPEND BASE = final data = univ; RUN; 375 376 %end ; 377 378 data QDC.acctcomp&year; set final; 379 380 proc sort; by gvkey_i datadate_i gvkey_j; 381 382 %mend loop; NOTE: The macro LOOP completed compilation without errors. 30 instructions 1888 bytes. 383 run; ... View more

152 %macro loop(year, nfirms); 153 OPTIONS MCOMPILENOTE=NOAUTOCALL; 154 OPTIONS SYMBOLGEN; 155 OPTIONS MPRINT; 156 /* Loop by firm &i for year &year. This will generate a dataset of 156! firm i-j AcctComp for each year. */ 157 158 data temp1a; set temp1; if year(datadate1)=&year; 159 proc sort; by gvkey1; 160 161 data nfirms; set temp1a; 162 keep gvkey1; 163 proc sort nodupkey; by gvkey1; 164 165 data nfirms; set nfirms; id = _N_; 166 167 data temp1a; merge temp1a nfirms; by gvkey1; 168 169 %do i= 1 %to &nfirms; 170 171 data est1; set temp1a; 172 proc sort nodupkey; by gvkey1; 173 174 data est2; set temp1a; if id = &i; 175 176 PROC SQL; 177 CREATE TABLE est2 AS SELECT 178 est2.*, est1.gvkey1 AS gvkey_j, est1.a_i AS a_j, est1.b_i AS b_j 179 FROM est2 LEFT JOIN est1 180 ON est2.gvkey1 ~= est1.gvkey1 181 AND est2.sic2 = est1.sic2; 182 QUIT; 183 184 data est2; set est2; 185 a_dif = a_i - a_j; 186 b_dif = b_i - b_j; 187 drop a_i a_j b_i b_j; 188 189 proc sort; by gvkey1 datadate1 fqenddt1 datacqtr1 gvkey_j; 190 191 data est2; set est2; 192 193 proc sort; by gvkey1 datadate1 fqenddt1 datacqtr1 gvkey_j fqenddt; 194 195 data est2; set est2; 196 error = ABS(a_dif + bhr * b_dif); 197 198 proc sort; by gvkey1 datadate1 gvkey_j fqenddt; 199 200 PROC UNIVARIATE NOPRINT; by gvkey1 datadate1 gvkey_j; 201 VAR error ; 202 OUTPUT OUT = univ N=n MEAN = me_error ; 203 204 data univ; set univ; 205 acctcomp = -1 * me_error*100; 206 acctcomp = round(acctcomp, 0.001); 207 208 rename gvkey1 = gvkey_i; 209 rename datadate1 = datadate_i; 210 211 if not missing (acctcomp); 212 if 14 <= n <= 16; 213 214 drop me_error n; 215 216 PROC APPEND BASE = final data = univ; RUN; 217 218 %end ; 219 220 data QDC.acctcomp&year; set final; 221 222 proc sort; by gvkey_i datadate_i gvkey_j; 223 224 %mend loop; NOTE: The macro LOOP completed compilation without errors. 30 instructions 1888 bytes. ... View more

Hi Jim, Thank you so much for your help. I re ran your code. But it didn't work. It didn't show any result. There are 236,480 records in the dataset and it is from 2014-2019. nfirms is based on the pair of the firm i and j. Is there anyway we can post the dataset so you may understand it better?  ... View more

Thank you, @jimbarbour. I have posted the log. There is no error in the log.  ... View more

1386 %macro loop(year, nfirms); 1387 /* Loop by firm &i for year &year. This will generate a dataset of 1387! firm i-j AcctComp for each year. */ 1388 1389 data temp1a; set temp1; if year(datadate1)=&year; 1390 proc sort; by gvkey1; 1391 1392 data nfirms; set temp1a; 1393 keep gvkey1; 1394 proc sort nodupkey; by gvkey1; 1395 1396 data nfirms; set nfirms; id = _N_; 1397 1398 data temp1a; merge temp1a nfirms; by gvkey1; 1399 1400 %do i= 1 %to &nfirms; 1401 1402 data est1; set temp1a; 1403 proc sort nodupkey; by gvkey1; 1404 1405 data est2; set temp1a; if id = &i; 1406 1407 PROC SQL; 1408 CREATE TABLE est2 AS SELECT 1409 est2.*, est1.gvkey1 AS gvkey_j, est1.a_i AS a_j, est1.b_i AS b_j 1410 FROM est2 LEFT JOIN est1 1411 ON est2.gvkey1 ~= est1.gvkey1 1412 AND est2.sic2 = est1.sic2; 1413 QUIT; 1414 1415 data est2; set est2; 1416 a_dif = a_i - a_j; 1417 b_dif = b_i - b_j; 1418 drop a_i a_j b_i b_j; 1419 1420 proc sort; by gvkey1 datadate1 fqenddt1 datacqtr1 gvkey_j; 1421 1422 data est2; set est2; 1423 1424 proc sort; by gvkey1 datadate1 fqenddt1 datacqtr1 gvkey_j fqenddt; 1425 1426 data est2; set est2; 1427 error = ABS(a_dif + bhr * b_dif); 1428 1429 proc sort; by gvkey1 datadate1 gvkey_j fqenddt; 1430 1431 PROC UNIVARIATE NOPRINT; by gvkey1 datadate1 gvkey_j; 1432 VAR error ; 1433 OUTPUT OUT = univ N=n MEAN = me_error ; 1434 1435 data univ; set univ; 1436 acctcomp = -1 * me_error*100; 1437 acctcomp = round(acctcomp, 0.001); 1438 1439 rename gvkey1 = gvkey_i; 1440 rename datadate1 = datadate_i; 1441 1442 if not missing (acctcomp); 1443 if 14 <= n <= 16; 1444 1445 drop me_error n; 1446 1447 PROC APPEND BASE = final data = univ; RUN; 1448 1449 %end ; 1450 1451 data QDC.acctcomp&year; set final; 1452 1453 proc sort; by gvkey_i datadate_i gvkey_j; 1454 1455 %mend loop; ... View more