I am running the code below on the attached .csv files; the code gives me my desired dataset (dataset CDebt9 below). Unfortunately, however, once I perform the "proc datasets" step below (see text in bold) I lose the values for a signinficant number of fields within the CDebt9 dataset; these value present in the preceding CDebt8 dataset. What can I do to avoid this?
Indicatively, the affected fields are the following (in the order that they appear under the first column in the CDebt8 and CDebt9 datasets: UnderwriterDelivery, FullPrice, PaymentDates, UnderwriterDiscount, MonthsBetweenBermudanCalls, OAS, PutNotificationDays, PriceToPublic, CallNotificationDays, CDates, MaturityDate, PutDates, FaceValues, FlatPrice, StepAnnualCouponRates, OriginationDate, PutStrikes, MonthsBetweenBermudanPuts, and CallStrikes.
options missing=' ';
data WORK.CDebt;
infile '/filepath/Test1.csv' dsd lrecl=40000 firstobs=3;
informat FullPrice 10.8;
informat Notional 12.;
informat ID $10.;
informat PriceToPublic 15.;
informat CallNotificationDays 10.8;
informat AccruedDayCount $15.;
informat InterestFrequency 5.;
informat MaturityDate mmddyy10.;
informat FlatPrice 10.8;
informat AnnualCouponRates 6.4;
informat OriginationDate mmddyy10.;
informat CallFeature $15.;
informat CallDiscrete $5.;
informat Putable $5.;
informat FirstCpnDt mmddyy10.;
informat Issuer $30.;
informat CouponType $30.;
format FullPrice 10.8
MaturityDate OriginationDate FirstCpnDt yymmddn8.;
input FullPrice Notional ID $ PriceToPublic CallNotificationDays
AccruedDayCount $ InterestFrequency MaturityDate FlatPrice
AnnualCouponRates OriginationDate CallFeature $ CallDiscrete $
Putable $ FirstCpnDt Issuer $ CouponType $;
Y=round(((MaturityDate-OriginationDate)/360),0.1);
X=MaturityDate;
W=intck('YEAR',OriginationDate,MaturityDate);
V=intck('MONTH',OriginationDate,FirstCpnDt);
/*if CouponType='FIXED' then delete;*/
run;
data WORK.CDebtSchedules;
infile '/filepath/CallSchedule.csv' dsd lrecl=40000 firstobs=2;
informat Cusip $10.;
informat CallDates mmddyy10.;
format CallDates yymmddn8.;
input Cusip CallDates;
run;
data WORK.StepRateSchedules;
infile '/filepath/RateSchedule.csv' dsd lrecl=40000 firstobs=2;
informat Cusip $10.;
informat AnnualCouponRateDates mmddyy10.;
informat AnnualCouponRates 5.;
format AnnualCouponRateDates yymmddn8.;
input Cusip AnnualCouponRateDates AnnualCouponRates;
run;
data CDebt2;
retain InstrumentName CouponDates ValuationDate DiscountSource
UnderwriterDelivery FullPrice Notional ID PaymentDayCount
CallType CouponProfile IndexSource CouponEntry PaymentDates
UnderwriterDiscount MonthsBetweenBermudanCalls OAS NotificationDate
PutNotificationDays PriceToPublic Cusip CallNotificationDays
AccruedDayCount PutEntry InterestFrequency CallDates MaturityDate
PutDates FaceValues CouponAdjusting FlatPrice Description PutType
BusinessDayAdjust Proceeds SecurityType CallEntry Name SettlementDate
Category AnnualCouponRates Structure DisableLegacyMeasures
OriginationDate PutStrikes MonthsBetweenBermudanPuts CallStrikes;
InstrumentName='CallablePutableBond';
ValuationDate='20130930';
DiscountSource='LiborSpot';
UnderwriterDelivery=-1;
IndexSource='LiborSpot';
CouponEntry='Manual';
UnderwriterDiscount=0;
OAS=0.0;
NotificationDate=' ';
PutNotificationDays=5;
PutEntry='Manual';
FaceValues=100;
CouponAdjusting='false';
BusinessDayAdjust='Following';
Proceeds=' ';
SecurityType=' ';
CallEntry='Manual';
SettlementDate='20130930';
MonthsBetweenBermudanPuts=-1;
DisableLegacyMeasures='false';
PutStrikes=100.0;
CallStrikes=100.0;
set CDebt;
CallDates=MaturityDate;
CouponDates=MaturityDate;
PaymentDates=MaturityDate;
Cusip=ID;
Name=ID;
PutDates=FirstCpnDt;
Z=put(X,mmddyy10.);
UnderwriterDiscount=PriceToPublic-FullPrice;
AnnualCouponRates=AnnualCouponRates/100;
Structure=COMPRESS(W||' nc '||V||'m');
if Notional=. then Notional=' ';
if CouponType='FIXED' then CouponProfile='F';
if Putable='N' then PutType='NoOption';
if index(AccruedDayCount,'30/360') then do;
AccruedDayCount='30/360-ISDA';
PaymentDayCount='30/360-ISDA';
end;
if index(AccruedDayCount,'ACT/360') then do;
AccruedDayCount='Act/360';
PaymentDayCount='Act/360';
end;
if index(AccruedDayCount,'ACT/ACT') then do;
AccruedDayCount='Act/Act';
PaymentDayCount='Act/Act';
end;
if CallFeature='Onetime' then do;
CallType='European';
MonthsBetweenBermudanCalls=0;
if index(Issuer,'FANNIE') then Category='AgencyDebt.Callable.FixedRate.Callable-European';
end;
if CallFeature='Quarterly' or CallFeature='Monthly' then do;
CallType='Bermudan';
MonthsBetweenBermudanCalls=3;
if index(Issuer,'FANNIE') then Category='AgencyDebt.Callable.FixedRate.Callable-Bermudan';
end;
if index(Issuer,'TREASURY') then
Description=COMPRESS('Treas '||AnnualCouponRates||'% '||Y||' YR '||Z);
else if index(Issuer,'FANNIE') then
Description=COMPRESS('FNMA '||AnnualCouponRates||'% '||Y||' YR '||Z);
else if index(Issuer,'VALLEY') then
Description=COMPRESS('TVA '||AnnualCouponRates||'% '||Y||' YR '||Z);
format CouponDates PaymentDates CallDates PutDates yymmddn8.;
run;
%let dsid=%sysfunc(open(CDebt2));
%let Nobs=%sysfunc(attrn(&dsid,nlobs));
%let dsid=%sysfunc(close(&dsid));
%macro WorkBench();
%do l=1 %to %eval(&Nobs);
data CDebt3;
set CDebt2(firstobs=&l obs=&l);
if InterestFrequency<>. then do;
if InterestFrequency=2 then interval='semiyear';
if InterestFrequency=12 then interval='month ';
intervals= intck(interval,FirstCpnDt,MaturityDate);
do i=1 to intervals;
CouponDates=intnx(interval,MaturityDate,i-intervals,'same');
PaymentDates=CouponDates;
var=InterestFrequency;
output;
end;
end;
else if InterestFrequency=. then do;
PaymentDates=MaturityDate;
CouponDates=OriginationDate;
var=InterestFrequency;
output;
end;
run;
data CDebt4;
set CDebt3(where=(InterestFrequency<>.) obs=1);
CouponDates=FirstCpnDt;
PaymentDates=FirstCpnDt;
run;
proc append base=CDebt4
data=CDebt3;
run;
data CDebt5;
retain InstrumentName CouponDates ValuationDate DiscountSource
UnderwriterDelivery FullPrice Notional ID PaymentDayCount
CallType CouponProfile IndexSource CouponEntry PaymentDates
UnderwriterDiscount MonthsBetweenBermudanCalls OAS NotificationDate
PutNotificationDays PriceToPublic Cusip CallNotificationDays
AccruedDayCount PutEntry InterestFrequency CallDates MaturityDate
PutDates FaceValues CouponAdjusting FlatPrice Description PutType
BusinessDayAdjust Proceeds SecurityType CallEntry Name SettlementDate
Category AnnualCouponRates Structure DisableLegacyMeasures
OriginationDate PutStrikes MonthsBetweenBermudanPuts CallStrikes;
set CDebt4 (drop=InterestFrequency);
if var in (2, .) then InterestFrequency='SemiAnnually';
if var=12 then InterestFrequency='Monthly';
drop Y X W V Z interval intervals i var;
call symput('Cusip',Cusip);
run;
/********************************/
/*****Adding CallSchedule********/
/********************************/
data CallSchedule (rename=(CallDates=CDates));
set Cdebtschedules;
where Cusip="&Cusip";
run;
data PaymentDates;
set Cdebt5;
keep Cusip PaymentDates;
run;
data PaymentCallDates;
merge Paymentdates CallSchedule;
by Cusip;
run;
proc sql;
create table CDebt6 as
select a.*, b.CDates
from CDebt5 a
left join PaymentCallDates b
on a.Cusip=b.Cusip
and a.PaymentDates=b.PaymentDates;
quit;
/**************************************/
/*****Finished with CallSchedule*******/
/**************************************/
/********************************/
/*****Adding RateSchedule********/
/********************************/
data RateSchedule;
set Steprateschedules ;
where Cusip="&Cusip";
run;
proc sort data=RateSchedule;
by Cusip AnnualCouponRateDates;
run;
data StepRates1;
format lowdate yymmddn8.;
set RateSchedule(rename=(AnnualCouponRateDates=CouponDates));
by Cusip coupondates;
lowdate = lag(coupondates)+1;
if first.Cusip then lowdate = "01Jan1800"d;
rename coupondates = highdate;
run;
proc sql;
create table CDebt7 as
select a.*,
b.AnnualCouponRates as StepAnnualCouponRates
from CDebt6 as a
left join StepRates1 as b
on a.cusip=b.cusip
and coupondates between lowdate and highdate
order by a.Cusip, a.Coupondates;
quit;
/***********************************/
/*****Finished with RateSchedule****/
/***********************************/
proc transpose data=CDebt7 out=CDebt8(rename=(_name_=var1));
var InstrumentName CouponDates ValuationDate DiscountSource
UnderwriterDelivery FullPrice Notional ID PaymentDayCount
CallType CouponProfile IndexSource CouponEntry PaymentDates
UnderwriterDiscount MonthsBetweenBermudanCalls OAS NotificationDate
PutNotificationDays PriceToPublic Cusip CallNotificationDays
AccruedDayCount PutEntry InterestFrequency CDates MaturityDate
PutDates FaceValues CouponAdjusting FlatPrice Description PutType
BusinessDayAdjust Proceeds SecurityType CallEntry Name SettlementDate
Category StepAnnualCouponRates Structure DisableLegacyMeasures
OriginationDate PutStrikes MonthsBetweenBermudanPuts CallStrikes;
run;
%let dsid=%sysfunc(open(CDebt8));
%let n=%sysfunc(attrn(&dsid,nvars));
%let dsid=%sysfunc(close(&dsid));
%let NCol=%eval(&n-1);
data End;
array COL{&NCol} $ COL1-COL&NCol;
do i=1 to dim(COL);
COL(i)='end';
end;
drop i;
run;
data StartingArray;
array COL{51} $50. COL0-COL50;
COL0='Delete';
rename COL0=var1;
run;
proc datasets library=work;
append base=StartingArray data=CDebt8 FORCE;
append base=StartingArray data=End FORCE;
quit;
proc append
base=CDebt9 data=StartingArray FORCE;
run;
%end;
%mend;
%WorkBench();
Click image to register for webinar
