How to implement the following in proc sql?   proc sort data = set1; by x, y; run; proc sort data = set2; by x, y; run; data set3; merge set1 (in = a) set2 (in = b); by x y; if a and not b; run; I am trying to do this by group, and the group identifier is commonly available in set1 and set2.    ... View more

I compute weighted average using the following proc sql; create table mytable as select sum(X * Y) / sum(Y) as wave, mean(X) as ave from my_dataset group by Z; quit; I realize that there are errors in computed weighted average "wave" when some values of variable "X" are missing within a group "Z", whereas "Y" are nonmissing. The denominator would be the sum of all nonmissing values of "Y", but the numerator is only the sum of nonmissing "X". Is there a good practice to solve this issue?  ... View more

I am trying to to calculate bond yield using SAS function YIELDP (link).    I followed the function's description to call:    ytm = yieldp(100, coupon / 100, 2, k, k0, price_dirty);   The function returns reasonable values for most observations, but I do get many error massages: NOTE: Argument 1 to function YIELDP(100,0.013,2,1,0.002739726,99.053592466) at line 2149 column 11 is invalid. offering_date=20141015 maturity=20170615 COUPON=1.3 CRSP_PORTNO=1026667 REPORT_DT=20161231 NBR_SHARES=10000000 MARKET_VAL=9839825.00 cusip_8digit=00507UAB cusip=00507UAB7 price=98.39825 rating_num=11 rating_date=20161208 rating_round=12 month=2016-12-31 rating_group=BBB k=1 k0=0.002739726 ai=0.6553424658 price_dirty=99.053592466 ytm=. _ERROR_=1 _N_=119156 NOTE: Argument 1 to function YIELDP(100,0.0511,2,4,0.1698630137,12.93) at line 2149 column 11 is invalid. offering_date=20010410 maturity=20190315 COUPON=5.11 CRSP_PORTNO=1031647 REPORT_DT=20170430 NBR_SHARES=507340 MARKET_VAL=57075.75 cusip_8digit=009451AP cusip=009451AP0 price=11.25 rating_num=1 rating_date=20141120 rating_round=2 month=2017-04-30 rating_group= k=4 k0=0.1698630137 ai=1.68 price_dirty=12.93 ytm=. _ERROR_=1 _N_=190181 NOTE: Argument 1 to function YIELDP(100,0.0511,2,4,0.0849315068,13.613999443) at line 2149 column 11 is invalid. offering_date=20010410 maturity=20190315 COUPON=5.11 CRSP_PORTNO=1023709 REPORT_DT=20170531 NBR_SHARES=727506 MARKET_VAL=83663.24 cusip_8digit=009451AP cusip=009451AP0 price=11.499999443 rating_num=1 rating_date=20170531 rating_round=2 month=2017-05-31 rating_group= k=4 k0=0.0849315068 ai=2.114 price_dirty=13.613999443 ytm=. _ERROR_=1 _N_=190182 The input values seem valid. Is there any reason behind these messages?  ... View more