topic Re: Converting Character to Date (Number) in SAS Programming

Converting Character to Date (Number)

maroulator — Wed, 10 Sep 2014 21:54:20 GMT

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

Re: Converting Character to Date (Number)

Patrick — Wed, 10 Sep 2014 22:00:10 GMT

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;

Re: Converting Character to Date (Number)

stat_sas — Wed, 10 Sep 2014 22:00:54 GMT

Try this.

data WANT;

set Data;

M1=input(InterestFrequency,8.);

M2=input(MaturityDate,date9.);

M3=input(FirstCpnDt,date9.);

run;

Re: Converting Character to Date (Number)

maroulator — Wed, 10 Sep 2014 22:09:36 GMT

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

Re: Converting Character to Date (Number)

maroulator — Wed, 10 Sep 2014 22:12:52 GMT

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

Re: Converting Character to Date (Number)

ballardw — Wed, 10 Sep 2014 23:23:19 GMT

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.

Re: Converting Character to Date (Number)

Tom — Wed, 10 Sep 2014 23:24:25 GMT

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

Re: Converting Character to Date (Number)

maroulator — Thu, 11 Sep 2014 01:02:06 GMT

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;