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;
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
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.
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
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;
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: