BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
maroulator
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

7 REPLIES 7
Patrick
Opal | Level 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;

maroulator
Obsidian | Level 7

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

stat_sas
Ammonite | Level 13

Try this.

data WANT;

   set Data;

   M1=input(InterestFrequency,8.);

  M2=input(MaturityDate,date9.);

   M3=input(FirstCpnDt,date9.);

run;

maroulator
Obsidian | Level 7

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

ballardw
Super User

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.

Tom
Super User Tom
Super User

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

maroulator
Obsidian | Level 7

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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