I have the attached output also shown below. This already exists in SAS, so my objective is not to import the .csv file into SAS; the .csv file is just attached for your convenience. The variables are all text/character variables and I am trying to convert them into numeric using the code below, however all that I get are numeric blanks ie ( . ).
Could someone please provide some insight?
data WANT;
set Data;
M1=input(InterestFrequency,3.);
M2=input(MaturityDate,ddmmyy8.);
M3=input(FirstCpnDt,ddmmyy8.);
run;
**********************************************************
**********************************************************
SAS DATASET
InterestFrequency MaturityDate FirstCpnDt
2 15May2041 15Nov2011
Could it be because the data in the file is formatted with DATE9 format instead of MMDDYY?
Do not look at the file with Excel as it will transform it.
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----
1 "InterestFrequency","MaturityDate","FirstCpnDt" 47
2 2,15MAY2041,15NOV2011 21
Try below (untested) code. If it still doesn't work then tell us what's in the SAS log.
data WANT;
set Data;
M1=input(InterestFrequency,best.);
M2=input(MaturityDate,date.);
M3=input(FirstCpnDt,date.);
attrib m2 m3 format=date9.;
run;
Patrick,
It didn't work; I am still getting blanks. Below is th elog.
4736 data WANT;
4737 set Data;
4738 M1=input(InterestFrequency,best.);
4739 M2=input(MaturityDate,date.);
4740 M3=input(FirstCpnDt,date.);
4741 attrib m2 m3 format=date9.;
4742 run;
NOTE: There were 1 observations read from the data set WORK.DATA.
NOTE: The data set WORK.WANT has 1 observations and 6 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
Try this.
data WANT;
set Data;
M1=input(InterestFrequency,8.);
M2=input(MaturityDate,date9.);
M3=input(FirstCpnDt,date9.);
run;
This didn't work either; I am still getting blanks. The log is similar to the one I responded with to Patrick. I have three new variables forming, but all show up with blank (numeric) values (ie ( . ) ).
How did you create the set DATA? If you used proc import it may have read the date and created SAS date variables that being displayed using Date format and all you really need is to change the format associated to mmddyy8.
Run proc contents on the set data and show the result.
Could it be because the data in the file is formatted with DATE9 format instead of MMDDYY?
Do not look at the file with Excel as it will transform it.
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----
1 "InterestFrequency","MaturityDate","FirstCpnDt" 47
2 2,15MAY2041,15NOV2011 21
Tom,
I can't thank you enough for your continued engagement. Below is the code that leads to the leads to the Data dataset mentioned above; in the code below the test2 dataset is the same as the Data dataset mentioned above. Are you saying that I should bring in ValuationDate and MaturityDate from the .csv file using an MMDDYY informat?
Also, relating to your question on my other posting ("Not sure why this is not working"); this is best explained in the last data step below (data test3), which is a (cleaner) variation of the code I have in the other posting. I am trying to define arrays of different lengths depending on whether the InterestFrequency variable assumes a value of 2 or 12; InterestFrequency represents the frequency of the coupon payments on a bond, so if the bond pays interest semiannually (ie InterestFrequency=2) it will have different cashflows (represented by the array) from a bond that pays interest monthly (and hence, has a different array associated with it, whereby InterestFrequency=12). As you can see, my entire "data test3" datastep hinges on my being able to convert InterestFrequency, MaturityDate, and ValuationDate into numeric variables so that I can get the datastep to run.
I hope this clarifies matters.
data WORK.NCDebt;
infile '/fmacdata/utility/fin/KBenchmarks/SAS Data/NCDebt_1.csv' dsd lrecl=40000 firstobs=3;
informat ValuationDate yymmdd.;
informat MaturityDate mmddyy10.;
informat InterestFrequency 5.;
informat AccruedDayCount $10.;
format ValuationDate date9. MaturityDate date9.;
input ValuationDate MaturityDate InterestFrequency AccruedDayCount $;
run;
proc transpose data=Ncdebt
out=Ncdebt2;
var ValuationDate MaturityDate InterestFrequency AccruedDayCount;
run;
data Ncdebt3;
set Ncdebt2(rename=(_NAME_=Var1));
keep Var1 COL1;
run;
proc sql;
select COL1
into: ValuationDate
from Ncdebt3
where Var1='ValuationDate';
quit;
proc sql;
select COL1
into: MaturityDate
from Ncdebt3
where Var1='MaturityDate';
quit;
%let Result=%sysevalf("&MaturityDate"d-"&ValuationDate"d);
%put &Result;
proc transpose data=Ncdebt3
out=test;
var var1 COL1;
id var1;
run;
data test2;
set test (firstobs=2);
keep MaturityDate ValuationDate InterestFrequency;
run;
data Test3;
set Test2;
if InterestFrequency='2' then do;
count1=(intck('semiyear',ValuationDate,MaturityDate)+1);
%let arrelements=count1;
array dt{&arrelements} dt1-dt&arrelements;
end;
else if InterestFrequency='12' then do;
count1=(intck('month',ValuationDate,MaturityDate)+1);
%let arrelements=count1;
array date{&arrelements} date1-date&arrelements;
end;
run;
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 25. 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.