proc summary data=compustat nway;
class firm;
var sales;
output out=_stats_ mean=average stddev=std;
run;
data want;
merge compustat _stats_;
by firm;
run;
The data set _stats_ that is created is the one you asked for with one record per firm.
... View more
I'm trying to piece together what exactly you want. Please post an example of the "want" dataset in the future. From what I understand, you want to calculate a rolling 12 month volatility for a number of stocks (but only if 9 of the 12 months are nonmissing). Adapting @s_lassen's response on this thread (https://communities.sas.com/t5/SAS-Programming/calculating-rolling-standard-deviation-of-stock-return/td-p/488095😞
data have;
infile cards expandtabs truncover;
input stock date : yymmn6. ret;
format date yymmn6.;
cards;
1 198501 -0.10435
1 198502 -0.04762
1 198503 -0.08154
1 198504 -0.06757
1 198505 -0.08911
1 198506 -0.05481
1 198507 -0.0628
1 198508 -0.0989
1 198509 -0.10811
1 198510 -0.14205
1 198511 -0.04286
1 198512 -0.05505
1 198601 -0.04
1 198602 -0.1023
1 198603 -0.0163
1 198604 -0.14465
1 198607 -0.07383
1 198608 -0.0315
1 198609 0.05495
1 198610 -0.15368
1 198611 -0.11494
1 198612 -0.03578
1 198705 -0.1234
2 198501 -0.10435
2 198502 -0.04762
2 198503 -0.08154
2 198504 -0.06757
2 198505 -0.08911
2 198506 -0.05481
2 198507 -0.0628
2 198508 -0.0989
2 198509 -0.10811
2 198510 -0.14205
2 198511 -0.04286
2 198512 -0.05505
2 198601 -0.04
2 198602 -0.1023
2 198603 -0.0163
2 198604 -0.14465
2 198607 -0.07383
2 198608 -0.0315
2 198609 0.05495
2 198610 -0.15368
2 198611 -0.11494
2 198612 -0.03578
2 198705 -0.1234
;
run;
proc sort data=have;
by stock date;
run;
proc timeseries data=have out=have_ts;
by stock;
id date interval=month;
var ret;
run;
data want;
set have_ts;
by stock;
array returns(0:11) 8 _temporary_;
if first.stock then call missing(of returns(*));
returns(mod(_n_,12))=ret;
if n(of returns(*))>9 then stdret=std(of returns(*));
run;
It's best to try to fill in missing months with observations so that the calculation works properly (this is why I first create a timeseries with the have set).
-unison
... View more
That might be an entirely different problem than you described. Is the actual issue that you have two tables that have the same number of rows (critical here) and just need to match them in order?
data work.table1;
input Date $ maturity ;
datalines;
01-Mar 1.2
01-Mar 2.3
01-Mar 3.1
02-Mar 1.4
02-Mar 1.6
02-Mar 2.7
03-Mar 1.1
03-Mar 2.3
03-Mar 3.4
;
data work.table2;
input Date_given $ maturity_given Rate ;
datalines;
01-Mar 1 1
01-Mar 2 2
01-Mar 3 4
02-Mar 1 5
02-Mar 2 8
02-Mar 3 9
04-Mar 1 3
04-Mar 2 5
04-Mar 3 7
;
data work.want;
merge work.table1 work.table2;
drop date_given maturity_given;
run;
If not then you I still think your problem description in incomplete.
... View more
in reference to Maturity = char(vname(_[i]), 1); if my variable name is "mm_100" then how do i extract the number from it ? for example in this case maturity=100 when get the rows
... View more
It's not a one-step process, it takes two steps.
PROC RANK with the option GROUPS=4 creates quartiles.
Then you take the output data set and split the data into the upper and lower quartiles.
... View more
Typically, when there is only one dependent variable in your model you use all the instrumental variables to instrument both endogenous variables; however, if you want to limit which instrumental variables are used to correct for the endogeneity of each endogenous explanatory variable you could use the following:
proc tmodel data=yourdata; eq.a = y - ka*a; eq.p = y - kp*p; fit a p / ols 2sls; instruments (a, b c d) (p, q r s); quit;
... View more
data work.delete;
input firm $ year profit;
datalines;
A 2001 90
A 2002 80
A 2003 70
A 2004 90
A 2005 60
B 2001 70
B 2002 80
B 2003 90
C 2001 70
C 2002 80
C 2003 90
C 2004 60
C 2005 80
D 2001 10
D 2002 20
;
data _null_;
if _n_=1 then do;
dcl hash H (dataset:'delete',ordered: "A", multidata:'y') ;
h.definekey ("firm") ;
h.definedata (all:'y') ;
h.definedone () ;
end;
_n_=0;
do until(last.firm);
set delete end=lr;
by firm year;
if first.year then _n_+1;
end;
if _n_<5 then h.remove();
if lr then h.output(dataset:'want');
run;
... View more
See if this gets you started;
data work.growth;
input firm $ sales year;
datalines;
A 2400 2001
A 2500 2002
A 2600 2003
A 2700 2004
A 2800 2005
B 1500 2001
B 1600 2002
B 1700 2003
B 1800 2004
B 1900 2005
C 3100 2001
C 3200 2002
C 3300 2003
C 3400 2004
C 3400 2005
;
data want;
set work.growth;
by firm;
lyr1 = lag1(sales);
lyr2 = lag2(sales);
lyr3 = lag3(sales);
if first.firm then count=0;
else count+1;
if count ge 1 then growth1yr = (sales - lyr1)/lyr1;
if count ge 2 then growth2yr = (sales - lyr2)/lyr2;
if count ge 3 then growth3yr = (sales - lyr3)/lyr3;
run;
This assumes that you data is sorted by FIRM and YEAR. Note the changes to your data step code to remove the errors.
LAG is a function to look at a previous value. The COUNT variable is reset each time the firm changes and indicates how many previous years may be available for use. I left the LYR1 etc variables in the data so you could see which ones are used in calculations.
... View more
