Converting Character to Date (Number)

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 122
Accepted Solution

Converting Character to Date (Number)

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

Attachment

Accepted Solutions
Solution
‎09-10-2014 07:24 PM
Super User
Super User
Posts: 7,076

Re: Converting Character to Date (Number)

Posted in reply to maroulator

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

View solution in original post


All Replies
Respected Advisor
Posts: 4,173

Re: Converting Character to Date (Number)

Posted in reply to maroulator

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;

Frequent Contributor
Posts: 122

Re: Converting Character to Date (Number)

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

Trusted Advisor
Posts: 1,230

Re: Converting Character to Date (Number)

Posted in reply to maroulator

Try this.

data WANT;

   set Data;

   M1=input(InterestFrequency,8.);

  M2=input(MaturityDate,date9.);

   M3=input(FirstCpnDt,date9.);

run;

Frequent Contributor
Posts: 122

Re: Converting Character to Date (Number)

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

Super User
Posts: 11,343

Re: Converting Character to Date (Number)

Posted in reply to maroulator

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.

Solution
‎09-10-2014 07:24 PM
Super User
Super User
Posts: 7,076

Re: Converting Character to Date (Number)

Posted in reply to maroulator

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

Frequent Contributor
Posts: 122

Re: Converting Character to Date (Number)

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 386 views
  • 6 likes
  • 5 in conversation