Thanks, Rick. Method 2 works! ... View more

Thanks, Paige.  Here is the log:   3513 proc datasets; 3514 delete x x1; 3515 run; NOTE: Deleting WORK.X (memtype=DATA). NOTE: Deleting WORK.X1 (memtype=DATA). 3516 3517 /* THIS MACRO COMPUTES THE NEWS FOR EACH INDUSTRY SEPARATELY */ 3518 3519 %macro myloop; 3520 %do j=1 %to 11; 3521 run; 3522 3523 data u; 3524 set vdc.res; 3525 if ind=&j; 3526 run; 3527 3528 data g; 3529 set vdc.cof; 3530 if ind=&j; 3531 run; 3532 3533 proc iml; 3534 /* READ THE VECTORS OF RESIDUALS INTO A MATRIX CALLED RES_MAT, AND THE VAR COEFFICIENT MATRIX INTO COF_MAT. 3535 THE MATRIX ID_MAT KEEPS THE OBSERVATION ORDER 3536 */ 3537 use u; 3538 read all var{ER EE EB} into res_mat; 3539 read all var{gvkey yearq} into id_mat; 3540 use g; 3541 read all var {dmLOGR1 dmLOGE1 dmLOGBM1} into cof_mat; 3542 e1={1 0 0}; 3543 e2={0 1 0}; 3544 ro=0.967; 3545 /* USING RES_MAT AND COF_MAT, COMPUTE LAMBDA1 (LAM1) AND LAMBDA2 (LAM2), AND DISCOUNTRATE NEWS (NR) AND 3546 EARNINGS NEW (NE) 3547 */ 3548 lam1=e1*ro*cof_mat*inv(I(3)-ro*cof_mat); 3549 lam2=e1+lam1; 3550 /* TO COMPUTE EARNINGS NEWS DIRECTLY, REPLACE THE LINE ABOVE WITH lam2=e2*inv(I(3)-ro*cof_mat); 3551 */ 3552 Nr=(lam1*res_mat`)`; 3553 Ne=(lam2*res_mat`)`; 3554 /* CREATE THE NEWS MATRIX FROM THE NR AND NE VECTORS, AND TRANSFER IT INTO A DATASET CALLED X. THE 3555 DATASET X CONTAINS THE OBSERVATION ID'S AS WELL AS THE NEWS ITEMS FOR THE INDUSTRY 3556 */ 3557 z=id_mat||Nr||Ne; 3558 create x from z; 3559 append from z; 3560 quit; 3561 3562 data x; 3563 set x; 3564 ind=&j; 3565 run; 3566 /* APPEND THE INDUSTRY INFORMATION IN X INTO A DATASET CALLED X1 WHICH CONTAINS THE NEWS ITEMS FOR 3567 ALL INDUSTRIES 3568 */ 3569 proc datasets library=work; 3570 append base=work.x1 data=work.x force; 3571 %end; 3572 %mend myloop; 3573 %myloop; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.09 seconds cpu time 0.06 seconds   NOTE: There were 22358 observations read from the data set VDC.RES. NOTE: The data set WORK.U has 1024 observations and 6 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds   NOTE: There were 33 observations read from the data set VDC.COF. NOTE: The data set WORK.G has 3 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds NOTE: IML Ready WARNING: Variable yearq excluded because of type mismatch with id_mat. ERROR: (execution) Numeric argument should be character. operation : || at line 4736 column 148 operands : id_mat, Nr, Ne id_mat 1024 rows 1 col (character, size 6) Nr 1024 rows 1 col (numeric) Ne 1024 rows 1 col (numeric) statement : ASSIGN at line 4736 column 136 ERROR: Matrix z has not been set to a value. statement : CREATE at line 4736 column 154 ERROR: No data set is currently open for output. statement : APPEND at line 4736 column 171 NOTE: Exiting IML. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE IML used (Total process time): real time 0.02 seconds cpu time 0.03 seconds   ERROR: File WORK.X.DATA does not exist. NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.X may be incomplete. When this step was stopped there were 0 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.00 seconds   NOTE: Appending WORK.X to WORK.X1. NOTE: BASE data set does not exist. DATA file is being copied to BASE file. NOTE: There were 0 observations read from the data set WORK.X. NOTE: The data set WORK.X1 has 0 observations and 1 variables. NOTE: PROCEDURE DATASETS used (Total process time): real time 0.08 seconds cpu time 0.04 seconds   NOTE: There were 22358 observations read from the data set VDC.RES. NOTE: The data set WORK.U has 196 observations and 6 variables. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time 0.01 seconds   NOTE: There were 33 observations read from the data set VDC.COF. NOTE: The data set WORK.G has 3 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.00 seconds NOTE: IML Ready WARNING: Variable yearq excluded because of type mismatch with id_mat. ERROR: (execution) Numeric argument should be character. operation : || at line 4745 column 148 operands : id_mat, Nr, Ne id_mat 196 rows 1 col (character, size 6) Nr 196 rows 1 col (numeric) Ne 196 rows 1 col (numeric) statement : ASSIGN at line 4745 column 136 ERROR: Matrix z has not been set to a value. statement : CREATE at line 4745 column 154 ERROR: No data set is currently open for output. statement : APPEND at line 4745 column 171 NOTE: Exiting IML. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE IML used (Total process time): real time 0.01 seconds cpu time 0.03 seconds   NOTE: There were 0 observations read from the data set WORK.X. NOTE: The data set WORK.X has 0 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds   NOTE: Appending WORK.X to WORK.X1. NOTE: There were 0 observations read from the data set WORK.X. NOTE: 0 observations added. NOTE: The data set WORK.X1 has 0 observations and 1 variables. NOTE: PROCEDURE DATASETS used (Total process time): real time 0.06 seconds cpu time 0.04 seconds   NOTE: There were 22358 observations read from the data set VDC.RES. NOTE: The data set WORK.U has 3450 observations and 6 variables. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time 0.00 seconds   NOTE: There were 33 observations read from the data set VDC.COF. NOTE: The data set WORK.G has 3 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.00 seconds NOTE: IML Ready WARNING: Variable yearq excluded because of type mismatch with id_mat. ERROR: (execution) Numeric argument should be character. operation : || at line 4754 column 148 operands : id_mat, Nr, Ne id_mat 3450 rows 1 col (character, size 6) Nr 3450 rows 1 col (numeric) Ne 3450 rows 1 col (numeric) statement : ASSIGN at line 4754 column 136 ERROR: Matrix z has not been set to a value. statement : CREATE at line 4754 column 154 ERROR: No data set is currently open for output. statement : APPEND at line 4754 column 171 NOTE: Exiting IML. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE IML used (Total process time): real time 0.01 seconds cpu time 0.01 seconds   NOTE: There were 0 observations read from the data set WORK.X. NOTE: The data set WORK.X has 0 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.00 seconds   NOTE: Appending WORK.X to WORK.X1. NOTE: There were 0 observations read from the data set WORK.X. NOTE: 0 observations added. NOTE: The data set WORK.X1 has 0 observations and 1 variables. NOTE: PROCEDURE DATASETS used (Total process time): real time 0.05 seconds cpu time 0.03 seconds   NOTE: There were 22358 observations read from the data set VDC.RES. NOTE: The data set WORK.U has 1447 observations and 6 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds   NOTE: There were 33 observations read from the data set VDC.COF. NOTE: The data set WORK.G has 3 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time 0.00 seconds NOTE: IML Ready WARNING: Variable yearq excluded because of type mismatch with id_mat. ERROR: (execution) Numeric argument should be character. operation : || at line 4763 column 148 operands : id_mat, Nr, Ne id_mat 1447 rows 1 col (character, size 6) Nr 1447 rows 1 col (numeric) Ne 1447 rows 1 col (numeric) statement : ASSIGN at line 4763 column 136 ERROR: Matrix z has not been set to a value. statement : CREATE at line 4763 column 154 ERROR: No data set is currently open for output. statement : APPEND at line 4763 column 171 NOTE: Exiting IML. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE IML used (Total process time): real time 0.02 seconds cpu time 0.03 seconds   NOTE: There were 0 observations read from the data set WORK.X. NOTE: The data set WORK.X has 0 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds   NOTE: Appending WORK.X to WORK.X1. NOTE: There were 0 observations read from the data set WORK.X. NOTE: 0 observations added. NOTE: The data set WORK.X1 has 0 observations and 1 variables. NOTE: PROCEDURE DATASETS used (Total process time): real time 0.07 seconds cpu time 0.03 seconds   NOTE: There were 22358 observations read from the data set VDC.RES. NOTE: The data set WORK.U has 7560 observations and 6 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds   NOTE: There were 33 observations read from the data set VDC.COF. NOTE: The data set WORK.G has 3 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds NOTE: IML Ready WARNING: Variable yearq excluded because of type mismatch with id_mat. ERROR: (execution) Numeric argument should be character. operation : || at line 4772 column 148 operands : id_mat, Nr, Ne id_mat 7560 rows 1 col (character, size 6) Nr 7560 rows 1 col (numeric) Ne 7560 rows 1 col (numeric) statement : ASSIGN at line 4772 column 136 ERROR: Matrix z has not been set to a value. statement : CREATE at line 4772 column 154 ERROR: No data set is currently open for output. statement : APPEND at line 4772 column 171 NOTE: Exiting IML. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE IML used (Total process time): real time 0.01 seconds cpu time 0.00 seconds   NOTE: There were 0 observations read from the data set WORK.X. NOTE: The data set WORK.X has 0 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.03 seconds   NOTE: Appending WORK.X to WORK.X1. NOTE: There were 0 observations read from the data set WORK.X. NOTE: 0 observations added. NOTE: The data set WORK.X1 has 0 observations and 1 variables. NOTE: PROCEDURE DATASETS used (Total process time): real time 0.07 seconds cpu time 0.04 seconds   NOTE: There were 22358 observations read from the data set VDC.RES. NOTE: The data set WORK.U has 1011 observations and 6 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds   NOTE: There were 33 observations read from the data set VDC.COF. NOTE: The data set WORK.G has 3 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds NOTE: IML Ready WARNING: Variable yearq excluded because of type mismatch with id_mat. ERROR: (execution) Numeric argument should be character. operation : || at line 4781 column 148 operands : id_mat, Nr, Ne id_mat 1011 rows 1 col (character, size 6) Nr 1011 rows 1 col (numeric) Ne 1011 rows 1 col (numeric) statement : ASSIGN at line 4781 column 136 ERROR: Matrix z has not been set to a value. statement : CREATE at line 4781 column 154 ERROR: No data set is currently open for output. statement : APPEND at line 4781 column 171 NOTE: Exiting IML. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE IML used (Total process time): real time 0.01 seconds cpu time 0.01 seconds   NOTE: There were 0 observations read from the data set WORK.X. NOTE: The data set WORK.X has 0 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.00 seconds   NOTE: Appending WORK.X to WORK.X1. NOTE: There were 0 observations read from the data set WORK.X. NOTE: 0 observations added. NOTE: The data set WORK.X1 has 0 observations and 1 variables. NOTE: PROCEDURE DATASETS used (Total process time): real time 0.06 seconds cpu time 0.04 seconds   NOTE: There were 22358 observations read from the data set VDC.RES. NOTE: The data set WORK.U has 1214 observations and 6 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.00 seconds   NOTE: There were 33 observations read from the data set VDC.COF. NOTE: The data set WORK.G has 3 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds NOTE: IML Ready WARNING: Variable yearq excluded because of type mismatch with id_mat. ERROR: (execution) Numeric argument should be character. operation : || at line 4790 column 148 operands : id_mat, Nr, Ne id_mat 1214 rows 1 col (character, size 6) Nr 1214 rows 1 col (numeric) Ne 1214 rows 1 col (numeric) statement : ASSIGN at line 4790 column 136 ERROR: Matrix z has not been set to a value. statement : CREATE at line 4790 column 154 ERROR: No data set is currently open for output. statement : APPEND at line 4790 column 171 NOTE: Exiting IML. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE IML used (Total process time): real time 0.01 seconds cpu time 0.01 seconds   NOTE: There were 0 observations read from the data set WORK.X. NOTE: The data set WORK.X has 0 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds   NOTE: Appending WORK.X to WORK.X1. NOTE: There were 0 observations read from the data set WORK.X. NOTE: 0 observations added. NOTE: The data set WORK.X1 has 0 observations and 1 variables. NOTE: PROCEDURE DATASETS used (Total process time): real time 0.05 seconds cpu time 0.03 seconds   NOTE: There were 22358 observations read from the data set VDC.RES. NOTE: The data set WORK.U has 995 observations and 6 variables. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time 0.01 seconds   NOTE: There were 33 observations read from the data set VDC.COF. NOTE: The data set WORK.G has 3 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds NOTE: IML Ready WARNING: Variable yearq excluded because of type mismatch with id_mat. ERROR: (execution) Numeric argument should be character. operation : || at line 4799 column 148 operands : id_mat, Nr, Ne id_mat 995 rows 1 col (character, size 6) Nr 995 rows 1 col (numeric) Ne 995 rows 1 col (numeric) statement : ASSIGN at line 4799 column 136 ERROR: Matrix z has not been set to a value. statement : CREATE at line 4799 column 154 ERROR: No data set is currently open for output. statement : APPEND at line 4799 column 171 NOTE: Exiting IML. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE IML used (Total process time): real time 0.01 seconds cpu time 0.01 seconds   NOTE: There were 0 observations read from the data set WORK.X. NOTE: The data set WORK.X has 0 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds   NOTE: Appending WORK.X to WORK.X1. NOTE: There were 0 observations read from the data set WORK.X. NOTE: 0 observations added. NOTE: The data set WORK.X1 has 0 observations and 1 variables. NOTE: PROCEDURE DATASETS used (Total process time): real time 0.07 seconds cpu time 0.04 seconds   NOTE: There were 22358 observations read from the data set VDC.RES. NOTE: The data set WORK.U has 367 observations and 6 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds   NOTE: There were 33 observations read from the data set VDC.COF. NOTE: The data set WORK.G has 3 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time 0.00 seconds NOTE: IML Ready WARNING: Variable yearq excluded because of type mismatch with id_mat. ERROR: (execution) Numeric argument should be character. operation : || at line 4808 column 148 operands : id_mat, Nr, Ne id_mat 367 rows 1 col (character, size 6) Nr 367 rows 1 col (numeric) Ne 367 rows 1 col (numeric) statement : ASSIGN at line 4808 column 136 ERROR: Matrix z has not been set to a value. statement : CREATE at line 4808 column 154 ERROR: No data set is currently open for output. statement : APPEND at line 4808 column 171 NOTE: Exiting IML. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE IML used (Total process time): real time 0.01 seconds cpu time 0.01 seconds   NOTE: There were 0 observations read from the data set WORK.X. NOTE: The data set WORK.X has 0 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds   NOTE: Appending WORK.X to WORK.X1. NOTE: There were 0 observations read from the data set WORK.X. NOTE: 0 observations added. NOTE: The data set WORK.X1 has 0 observations and 1 variables. NOTE: PROCEDURE DATASETS used (Total process time): real time 0.05 seconds cpu time 0.01 seconds   NOTE: There were 22358 observations read from the data set VDC.RES. NOTE: The data set WORK.U has 4322 observations and 6 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds   NOTE: There were 33 observations read from the data set VDC.COF. NOTE: The data set WORK.G has 3 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds NOTE: IML Ready WARNING: Variable yearq excluded because of type mismatch with id_mat. ERROR: (execution) Numeric argument should be character. operation : || at line 4817 column 148 operands : id_mat, Nr, Ne id_mat 4322 rows 1 col (character, size 6) Nr 4322 rows 1 col (numeric) Ne 4322 rows 1 col (numeric) statement : ASSIGN at line 4817 column 136 ERROR: Matrix z has not been set to a value. statement : CREATE at line 4817 column 154 ERROR: No data set is currently open for output. statement : APPEND at line 4817 column 171 NOTE: Exiting IML. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE IML used (Total process time): real time 0.01 seconds cpu time 0.01 seconds   NOTE: There were 0 observations read from the data set WORK.X. NOTE: The data set WORK.X has 0 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.00 seconds   NOTE: Appending WORK.X to WORK.X1. NOTE: There were 0 observations read from the data set WORK.X. NOTE: 0 observations added. NOTE: The data set WORK.X1 has 0 observations and 1 variables. NOTE: PROCEDURE DATASETS used (Total process time): real time 0.05 seconds cpu time 0.04 seconds   NOTE: There were 22358 observations read from the data set VDC.RES. NOTE: The data set WORK.U has 772 observations and 6 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds   NOTE: There were 33 observations read from the data set VDC.COF. NOTE: The data set WORK.G has 3 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.00 seconds NOTE: IML Ready WARNING: Variable yearq excluded because of type mismatch with id_mat. ERROR: (execution) Numeric argument should be character. operation : || at line 4826 column 148 operands : id_mat, Nr, Ne id_mat 772 rows 1 col (character, size 6) Nr 772 rows 1 col (numeric) Ne 772 rows 1 col (numeric) statement : ASSIGN at line 4826 column 136 ERROR: Matrix z has not been set to a value. statement : CREATE at line 4826 column 154 ERROR: No data set is currently open for output. statement : APPEND at line 4826 column 171 NOTE: Exiting IML. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE IML used (Total process time): real time 0.01 seconds cpu time 0.00 seconds   NOTE: There were 0 observations read from the data set WORK.X. NOTE: The data set WORK.X has 0 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.00 seconds 3574 3575 3576 /* AT THE END OF THE LOOP TRANSFER THE ENTIRE NEWS DATA INTO A PERMANENT DATA CALLED NEWS AND 3577 COMPUTE THE VARIANCES 3578 */ NOTE: Appending WORK.X to WORK.X1. NOTE: There were 0 observations read from the data set WORK.X. NOTE: 0 observations added. NOTE: The data set WORK.X1 has 0 observations and 1 variables. NOTE: PROCEDURE DATASETS used (Total process time): real time 0.09 seconds cpu time 0.06 seconds 3579 data cashdrnews; 3580 set x1; 3581 rename col1=gvkey col2=yearq col3=nr col4=ne; 3582 run; WARNING: The variable col1 in the DROP, KEEP, or RENAME list has never been referenced. WARNING: The variable col2 in the DROP, KEEP, or RENAME list has never been referenced. WARNING: The variable col3 in the DROP, KEEP, or RENAME list has never been referenced. WARNING: The variable col4 in the DROP, KEEP, or RENAME list has never been referenced. NOTE: There were 0 observations read from the data set WORK.X1. NOTE: The data set WORK.CASHDRNEWS has 0 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 3583 data vdc.cashdrnews; 3584 set cashdrnews; 3585 vr=nr**2; 3586 ve=ne**2; 3587 covre=nr*ne; 3588 run; NOTE: Variable nr is uninitialized. NOTE: Variable ne is uninitialized. NOTE: There were 0 observations read from the data set WORK.CASHDRNEWS. NOTE: The data set VDC.CASHDRNEWS has 0 observations and 6 variables. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time 0.01 seconds   ... View more

Sorry for the late reply. Thanks. ... View more

Sorry for the late reply. The solution works! Thanks. ... View more

One minor correction to the message above: For the second sentence of the second para, the figure should be 450 firms not 400 firms. ... View more

Sorry, my message is probably not clear. Let me clarify.   I have around daily data on about 500 firms over a period of 15 years. What I need to is to form a final dataset where the firms included each year have a market cap greater than $5m.    Let's say that there are 500 firms in 2015 and applying a simple rule, I find that 50 of them have a mktcap that is less than $5m.  This means that, for 2015, the dataset would have daily data on only the 400 firms with a mktcap greater than $5m.  The simple selection rule is to base the mktcap of the firm on its year-end value (last value of the year) and only select those with mktcap>$5m.    By that token, a firm excluded in the earlier years when its mktcap < $5m, may be included in later years when its mktcap exceeds $5m.   So, the final dataset should only contain daily data on all firms with mktcap greater than $5m.    I hope this is clearer. Thanks.          ... View more

Dear Bart, I tried the two suggested codes on the full dataset but the final dataset (WANT_DAILY) still has firms with a mktcap of less than $5m. ... View more

Dear Yabwon.   Attached is the test data RET for three firms, one of which, has the first few years where MKTCAP<5. The other two firms are large MKTCAP firms where MKTCAP>5 for all years.  Thanks.   ... View more

Thanks v much. The code does the job nicely! ... View more

Thank you for pointing this out. When even the calendar matches, then the last resort is to look at which quarter is most recent. In this case, it is Q2, so observation 19 should be chosen. ... View more

Thanks, Shmuel. ... View more

Thanks, Novinosrin. ... View more

Sorry, the wrong SAS code was attached. Please use this one instead. Thanks. proc sql; create table temp5 as /****************************/ /*obs with 1 record per year*/ select * from testfile group by gvkey, year(datadate), qtr(datadate) having count(qtr(datadate))=1 /****************************/ union all corr /*********************************************************************/ /*obs with >1 record per year and all records with > 1 missing values*/ select a.* from testfile as a right join (select gvkey, max(datadate) as Date2 format=DDMMYY10. /* Min Max ??*/ from testfile group by gvkey, year(datadate), qtr(datadate) having count(qtr(datadate))>1 and min(nmiss(atq,cheq,ltq,revtq)) > 1) as b on a.gvkey=b.gvkey and a.datadate=b.Date2 /*********************************************************************/ union all corr /*********************************************************************/ /*obs with >1 record per year and at least one record with 0 or 1 missing value*/ select a.* from testfile as a right join (select gvkey, max(datadate) as Date2 format=DDMMYY10. from (select * from testfile group by gvkey, year(datadate), qtr(datadate) having count(qtr(datadate))>1 and min(nmiss(atq,cheq,ltq,revtq)) <= 1) where nmiss(atq,cheq,ltq,revtq)<= 1 group by gvkey, year(datadate), qtr(datadate)) as b on a.gvkey=b.gvkey and a.datadate=b.Date2; quit; ... View more