"Back-filling" arrays

[maroulator]

I have been working with the following code which is applied to the attached NCDebt.csv dataset; I am only loading one record into SAS. The code does exactly what I need it to do up until the macro definition; the macro definition represents my initial (incomplete) attempt at what it is that I am trying to achieve. What I am trying to achieve is to make the array in the NCDebt3 dataset look exactly like the one in the attached "Desired Array.csv" file. Because of the specifications of my business problem, I need to "back-fill" the array in NCDebt3; this means that in order for me to get the array that I want (again see attached "Desired Array.csv" file), I need to start with the value in date60 and work my way down to date1.

Any insight would be extremely appreciated. Many thanks in advance.

data WORK.NCDebt;
     infile '/FilePath/NCDebt.csv' dsd lrecl=40000 firstobs=3 obs=3;
informat InstrumentName $15.;
informat PriceToPublic 10.8;
informat Cusip $15.;
informat CouponDates mmddyy10.;
informat ValuationDate yymmdd.;
informat AccruedDayCount $10.;
informat FullPrice 10.8;
informat InterestFrequency 5.;
informat Notional 12.;
informat MaturityDate mmddyy10.;
informat ID $10.;
informat FaceValues 8.;
informat PaymentDayCount $10.;
informat FlatPrice 10.8;
informat Description $30.;
informat Comments $30.;
informat MarketCleanPrice 10.8;
informat SettlementDate yymmdd.;
informat Name $20.;
informat Category $40.;
informat PaymentDates mmddyy10.;
informat AnnualCouponRates 6.4;
informat OriginationDate mmddyy10.;
informat OAS 5.2;
informat FirstCpnDt mmddyy10.;
format PriceToPublic 10.8;
format FullPrice 10.8;
format CouponDates MaturityDate ValuationDate PaymentDates OriginationDate
       SettlementDate FirstCpnDt date9.;
input InstrumentName $ PriceToPublic Cusip $ CouponDates ValuationDate
      AccruedDayCount $ FullPrice InterestFrequency Notional MaturityDate
      ID $ FaceValues PaymentDayCount $ FlatPrice Description $ Comments $
      MarketCleanPrice SettlementDate Name $ Category $ PaymentDates 
      AnnualCouponRates OriginationDate OAS FirstCpnDt;
ID=Cusip;
Name=Cusip;
run;

data NCDebt2;
  retain InstrumentName PriceToPublic Cusip CouponDates ValuationDate
         AccruedDayCount DiscountSource FullPrice UnderWriterDelivery
         InterestFrequency Notional MaturityDate ID FaceValues
         PaymentDayCount CouponAdjusting FlatPrice Description Comments
         MarketCleanPrice BusinessDayAdjust CouponEntry SettlementDate Name Category
         PaymentDates AnnualCouponRates UnderwriterDiscount DisableLegacyMeasures
         OriginationDate OAS FirstCpnDt;
         DiscountSource='OIS';
         UnderWriterDelivery=-1;
         CouponAdjusting='false';
         BusinessDayAdjust='Following';
         CouponEntry='Manual';
  UnderwriterDiscount=0.0;
         DisableLegacyMeasures='false';
         format UnderwriterDiscount 3.1;
         set NCDebt;
  Result= MaturityDate-ValuationDate;
  if InterestFrequency=2 then do;
    arrelements=(intck('semiyear',FirstCpnDt,MaturityDate)+1);
  end;
  else if InterestFrequency=12 then do;
    arrelements=(intck('month',FirstCpnDt,MaturityDate)+1);
  end;
  call symput('arrelements',arrelements);
  drop Result arrelements;
run;

%put &arrelements;

data NCDebt3;
   set NCDebt2;
   array date{&arrelements} date1-date%eval(&arrelements);
   date%eval(&arrelements)=MaturityDate;
   date1=FirstCpnDt;
   format date1 date%eval(&arrelements) date9.;
run;

%macro Cashflows();

data NCDebt4;
   set NCDebt3;
   %do k=0 %to 2;
     %do i=1 %to 3;
       if month(date%eval(&arrelements))>6 then do;
          %let month=%sysfunc(month(date%eval(&arrelements-&i)))-6;
          %let year=%sysfunc(year(date%eval(&arrelements-&k)));
          %put &month &year;
       end;
       else if month(date%eval(&arrelements))<6 then do;
          %let month=%sysfunc(month(date%eval(&arrelements)))+6;
          %let year=%sysfunc(year(date%eval(&arrelements-&i)))-1;
          %put &month &year;
       end;
     %end;
   %end;
run;

%mend Cashflows;

%Cashflows;

[Reeza]

I've seen several questions from you on this same topic and haven't responded. Looking at it though, I really think there's a better way to solve your programming problem than the method you're using. 

For one, SAS has several financial functions that may be useful, as well as the fact that going down rows rather than across an array may be an easier method.  Then you can transpose at the end to get your desired format.

[Tom · Accepted solution]

I think you have finally posted something this is close to a description of your actual problem.

You have a first date and a last date and you want to generate all of the dates in between.  The interval to use for the dates can either be monthly or semi-annually.

First thing you should do is forget that SAS has  macro processing features and just solve your programming task using data steps and procs.

Second is reduce the problem to the part you need to solve. In this case we don't care how you are reading in your original data to find the two dates and the interval.  So we can just start with a simple data step to create an example data record.

data have ;

  FirstCpnDt='31MAR2011'd;

  InterestFrequency=2;

  MaturityDate='15MAY2041'd;

   format FirstCpnDt MaturityDate date9.;

run;

Now you want to find out how many intervals it will take to go from  FirstCpnDt to MaturityDate.  You can use the INTCK() function for this. (Note no macro logic is need to preform simple arithmetic or even complex date function calculations using SAS.). Then you want to generate all of the dates. So use a DO loop and generate the dates using the INTNX() function.  You DO NOT NEED to create multiple columns, just output multiple rows.

data want ;

   set have ;

   if InterestFrequency=2  then interval='semiyear';

   if InterestFrequency=12 then interval='month   ';

   intervals= intck(interval,FirstCpnDt,MaturityDate);

   do i=1 to intervals;

     date=intnx(interval,MaturityDate,i-intervals,'same');

     output;

     format date date9.;

  end;

run;

Note that this data step will work just as well for an input file with 1000 date pairs as it does for this simple example with just one observation.

If you did want to generate that strange CSV file with one row and 60 columns then you can just use PROC TRANSPOSE.  (Note that to process all of your data this way make sure to add a BY statement to PROC TRANSPOSE to include your id variables in the new horizontal dataset.)

proc transpose data=want out=dont_want(drop=_name_) prefix=date ;

  id i ;

  var date;

run;

data _null_;

  set dont_want ;

  put (_all_) (=/);

run;

date1=15NOV2011

date2=15MAY2012

date3=15NOV2012

date4=15MAY2013

date5=15NOV2013

date6=15MAY2014

date7=15NOV2014

date8=15MAY2015

date9=15NOV2015

date10=15MAY2016

date11=15NOV2016

date12=15MAY2017

date13=15NOV2017

date14=15MAY2018

date15=15NOV2018

date16=15MAY2019

date17=15NOV2019

date18=15MAY2020

date19=15NOV2020

date20=15MAY2021

date21=15NOV2021

date22=15MAY2022

date23=15NOV2022

date24=15MAY2023

date25=15NOV2023

date26=15MAY2024

date27=15NOV2024

date28=15MAY2025

date29=15NOV2025

date30=15MAY2026

date31=15NOV2026

date32=15MAY2027

date33=15NOV2027

date34=15MAY2028

date35=15NOV2028

date36=15MAY2029

date37=15NOV2029

date38=15MAY2030

date39=15NOV2030

date40=15MAY2031

date41=15NOV2031

date42=15MAY2032

date43=15NOV2032

date44=15MAY2033

date45=15NOV2033

date46=15MAY2034

date47=15NOV2034

date48=15MAY2035

date49=15NOV2035

date50=15MAY2036

date51=15NOV2036

date52=15MAY2037

date53=15NOV2037

date54=15MAY2038

date55=15NOV2038

date56=15MAY2039

date57=15NOV2039

date58=15MAY2040

date59=15NOV2040

date60=15MAY2041

[maroulator]

Tom,

I appreciate the feedback and solution. Below is my code so far after my transposition; I am having problems with the do-loop in my last datastep, however. My goal is to retain the values in the COL1 variable and set to blank all other observations (except those in the 'in' statement) in the COL2 to COL10 variables.

Could you please take a look?

data WORK.NCDebt;
     infile '/FilePath/NCDebt.csv' dsd lrecl=40000 firstobs=3 obs=3;
informat InstrumentName $15.;
informat PriceToPublic 10.8;
informat Cusip $15.;
informat CouponDates mmddyy10.;
informat ValuationDate yymmdd.;
informat AccruedDayCount $10.;
informat FullPrice 10.8;
informat InterestFrequency 5.;
informat Notional 12.;
informat MaturityDate mmddyy10.;
informat ID $10.;
informat FaceValues 8.;
informat PaymentDayCount $10.;
informat FlatPrice 10.8;
informat Description $30.;
informat Comments $30.;
informat MarketCleanPrice 10.8;
informat SettlementDate yymmdd.;
informat Name $20.;
informat Category $40.;
informat PaymentDates mmddyy10.;
informat AnnualCouponRates 6.4;
informat OriginationDate mmddyy10.;
informat OAS 5.2;
informat FirstCpnDt mmddyy10.;
format PriceToPublic 10.8;
format FullPrice 10.8;
format CouponDates MaturityDate ValuationDate PaymentDates OriginationDate
       SettlementDate FirstCpnDt date9.;
input InstrumentName $ PriceToPublic Cusip $ CouponDates ValuationDate
      AccruedDayCount $ FullPrice InterestFrequency Notional MaturityDate
      ID $ FaceValues PaymentDayCount $ FlatPrice Description $ Comments $
      MarketCleanPrice  &intSettlementDate Name $ Category $ PaymentDates 
      AnnualCouponRates OriginationDate OAS FirstCpnDt;
ID=Cusip;
Name=Cusip;
run;

data NCDebt2;
  retain InstrumentName PriceToPublic Cusip CouponDates ValuationDate
         AccruedDayCount DiscountSource FullPrice UnderWriterDelivery
         InterestFrequency Notional MaturityDate ID FaceValues
         PaymentDayCount CouponAdjusting FlatPrice Description Comments
         MarketCleanPrice BusinessDayAdjust CouponEntry SettlementDate Name Category
         PaymentDates AnnualCouponRates UnderwriterDiscount DisableLegacyMeasures
         OriginationDate OAS FirstCpnDt;
         DiscountSource='OIS';
         UnderWriterDelivery=-1;
         CouponAdjusting='false';
         BusinessDayAdjust='Following';
         CouponEntry='Manual';
  UnderwriterDiscount=0.0;
         DisableLegacyMeasures='false';
         format UnderwriterDiscount 3.1;
         set NCDebt;
run;

data NCDebt3;
   set NCDebt2;
   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;
     output;
     format CouponDates PaymentDates date9.;
   end;
   %let FirstCpnDt=FirstCpnDt;
   %let intervals=intervals;
run;

%put &FirstCpnDt &intervals;

proc transpose data=NCDebt3 out=NCDebt4(rename=(_name_=var1)) ;
var InstrumentName PriceToPublic Cusip CouponDates ValuationDate
     AccruedDayCount DiscountSource FullPrice UnderWriterDelivery
     InterestFrequency Notional MaturityDate ID FaceValues
     PaymentDayCount CouponAdjusting FlatPrice Description Comments
     MarketCleanPrice BusinessDayAdjust CouponEntry SettlementDate
     Name Category PaymentDates AnnualCouponRates UnderwriterDiscount
     DisableLegacyMeasures OriginationDate OAS;
run;

data NCDebt5;
  set NCDebt4;
  %let i=2;
    if var1 not in ('CouponDates','FaceValues','PaymentDates','AnnualCouponRates') then do;
      do i=%eval(&i) to 10;
         COL%eval(&i)=' ';
      end;
    end;
run;

%put &i;

[Tom]

First step is to move all pure macro statements (like %LET)  out of the middle of your SAS steps.  SAS will evaluate the macro code BEFORE it begins executing the SAS code.  Mixing them into the middle of the SAS steps is causing you to get confused.

For example in your third step you have two %LET statements just before the RUN statement.

%let FirstCpnDt=FirstCpnDt;

%let intervals=intervals;

Those should be before the DATA statement as they are not something this is going to happen while the data step is running. In fact you could put them at the top of the program since they are just setting the macro variables to constant strings.  And what value does it add to your program to set the macro variable INTERVALS to the character string intervals?

Also let's look at your last data step.  Since you are setting the macro variable I to 2 in the %LET statement the SAS code you are generating is just this.

data NCDebt5;

  set NCDebt4;

  if var1 not in ('CouponDates','FaceValues','PaymentDates','AnnualCouponRates') then do;

      do i=2 to 10;

         COL2 =' ';

      end;

  end;

run;

So depending on the value of VAR1 you either do nothing to the variable COL2 or you repeatedly set it to blank.

[maroulator]

Tom,

I have completed my code which you can find in the discussion titled Proc Export - Issues with Exporting to .txt files. I hope that this gives you an idea of my final product and I also hope to stay in your good graces.