Trouble with proc datasets/proc append

maroulator — Thu, 25 Sep 2014 22:32:38 GMT

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();

Re: Trouble with proc datasets/proc append

ballardw — Thu, 25 Sep 2014 23:18:44 GMT

The most likely cause would be mismatched data types for some of the variables. I'm going to go on a limb and say that you want to look at STARTINGARRAY and that the numeric variables are missing for COL0 through COL50 in CDebt8.

Run the append code without force and see if you messages about character/numeric issues.

topic Trouble with proc datasets/proc append in SAS Programming

Trouble with proc datasets/proc append

Re: Trouble with proc datasets/proc append