my little joys: March 2007

initial stages of merging data for my thesis...

*to merge bvfinancial and bvindustrial;
DATA bvcombined;
SET rawdat.bvfinancial rawdat.bvindustrial;
BY COID;
run;
data bvcombined; set bvcombined;
FISCAL=FISCAL*1;
run;
*to retain obs with fiscal month 1-6;
data bvcombined_2; set bvcombined;
fiscalyear=INT(FISCAL/100);
fiscalmonth=FISCAL-fiscalyear*100;
if fiscalmonth=1 or fiscalmonth=2 or fiscalmonth=3 or fiscalmonth=4 or fiscalmonth=5 or fiscalmonth=6;
run;
data bvcombined_2; set bvcombined_2;
yearcounter=fiscalyear-1987;
RUN;
*set SE to the relevant financial/industrial companies;
data bvcombined_2; set bvcombined_2;
IF BAL21=. THEN BEE=BALB13;ELSE BEE=BAL21;
IF BAL21=. THEN BEEE=BALB13;ELSE BEEE=BAL21;
IF BALB6=. THEN TA=BAL9;ELSE TA=BALB6;
IF BALB6=. THEN TAA=BAL9;ELSE TAA=BALB6;
IF BALB10=. THEN TL=BAL17 ;ELSE TL=BALB10;
IF BALB10=. THEN TLL=BAL17 ;ELSE TLL=BALB10;
IF INCB1=. THEN Sales1=INC1;ELSE Sales1=INCB1;
IF INCB1=. THEN Sales2=INC1;ELSE Sales2=INCB1;
run;
data uniqueid;set bvcombined_2
(keep = COID CONAME INDID YEAR BALB13 BAL21 fiscalyear fiscalmonth yearcounter BEE BEEE TA TAA TL TLL Sales1 Sales2);
run;
proc sql;
create table newcombined as
select
A.COID, A.CONAME, A.INDID, A.YEAR,A.BALB13, A.BAL21,A.fiscalyear,A.fiscalmonth,
A.yearcounter, B.BEE, C.BEEE, B.TA, C.TAA, B.TL, C.TLL, B.Sales1, C.Sales2
from uniqueid A, uniqueid B, uniqueid C
where
A.COID = B.COID=C.COID
and
A.yearcounter = B.yearcounter+1=C.yearcounter+2;
quit;
proc sort data= newcombined;
by COID year;
run;
data test; set returns.cleanreturn;
run;
proc sort data=test;
by COID monthcounter;
run;
data mergedbvret ;
merge newcombined test;
by COID year;
run;
*Set BEMEratios;
data mergedbvret;set mergedbvret;
If fiscalmonth=1 and month>6 AND month<13>0 THEN BEMEratio=BEE/MKTVAL;
If fiscalmonth=1 and month>0 AND month<7>0 THEN BEMEratio=BEEE/MKTVAL;
If fiscalmonth=2 and month>7 AND month<13>0 THEN BEMEratio=BEE/MKTVAL;
If fiscalmonth=2 and month>0 AND month<8>0 THEN BEMEratio=BEEE/MKTVAL;
If fiscalmonth=3 and month>8 AND month<13>0 THEN BEMEratio=BEE/MKTVAL;
If fiscalmonth=3 and month>0 AND month<9>0 THEN BEMEratio=BEEE/MKTVAL;
If fiscalmonth=4 and month>9 AND month<13>0 THEN BEMEratio=BEE/MKTVAL;
If fiscalmonth=4 and month>0 AND month<10>0 THEN BEMEratio=BEEE/MKTVAL;
If fiscalmonth=5 and month>10 AND month<13>0 THEN BEMEratio=BEE/MKTVAL;
If fiscalmonth=5 and month>0 AND month<11>0 THEN BEMEratio=BEEE/MKTVAL;
If fiscalmonth=6 and month>11 AND month<13>0 THEN BEMEratio=BEE/MKTVAL;
If fiscalmonth=6 and month>0 AND month<12>0 THEN BEMEratio=BEEE/MKTVAL;
RUN;
proc sort data=mergedbvret;
by BEMEratio;
run;
*Delete BEMEratios with missing values;
data mergedbvret;set mergedbvret;
if BEMEratio='.' then delete;
run;
*320139 obs;
data mergedbvret_1_6;set mergedbvret;
run;