Not sure why this is not working

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 120
Accepted Solution

Not sure why this is not working


I have the following code that is applied to the attached .csv file. Everything seems to work fine until the very last datastep; see note below. The InterestFrequency variable has a value of 2 before entering the last data step and has been converted to a character variable (it is initially a numeric variable) after the proc transpose. Any insight would be greatly appreciated.

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-"&FirstCpnDate"d);
%put &Result;


data Test1;
   set Ncdebt3;
   where Var1='InterestFrequency';
run;

data Test2;
   set Test1;
   if COL1='2' then do; -----------------------> I am looking for this 'if statement' to prevail between the two in this data step......
    %let arrelements=%sysevalf(%sysevalf(((&Result/(360/2)),ceil)+1);
    array dt{&arrelements} dt1-dt&arrelements;
   end;
   else if COL1='12' then do; --------------> ....instead, this is the one that prevails, completely ignoring the one before it, despite the fact that InterestFrequency=2 (text value).
    %let arrelements=%eval(&Result/(360/12));
    array date{&arrelements} date1-date&arrelements;
   end;
run;

Attachment

Accepted Solutions
Solution
‎09-11-2014 12:08 PM
Esteemed Advisor
Posts: 7,280

Re: Not sure why this is not working

As in the code I posted, count1 was calculated using your formula:

count1=(intck('semiyear',input(ValuationDate,date9.),input(MaturityDate,date9.))+1);


and the result was passed to a macro variable, &arrelements., with the following call:

  call symput('arrelements',count1);

The value passed was 55.

View solution in original post


All Replies
Respected Advisor
Posts: 3,775

Re: Not sure why this is not working

I expect the value is ' 2' space followed by 2.

Frequent Contributor
Posts: 120

Re: Not sure why this is not working

Sorry, that doesn't seem to be it. I tried the following before at the penultimate data step (data Test1) and got the same (undesirable) result once I ran the final data step (data Test2).

data Test1;

   set Ncdebt3;

   where var1='InterestFrequency';

   var2=trim(var2);

run;

Respected Advisor
Posts: 3,775

Re: Not sure why this is not working

Trim would have no effect on a leading space.  Perhaps STRIP or LEFT would be more useful.

Frequent Contributor
Posts: 120

Re: Not sure why this is not working

Unfortunately, STRIP or LEFT did not work.

Respected Advisor
Posts: 3,775

Re: Not sure why this is not working

Show your work.

Frequent Contributor
Posts: 120

Re: Not sure why this is not working

data Test1;

   set Ncdebt3;

   where var1='InterestFrequency';

   var2=left(var2); OR var2=strip(var2);

run;

Respected Advisor
Posts: 3,775

Re: Not sure why this is not working

Perhaps you are expecting something to happen here that isn't happening.  The IF ELSE IF blocks do not contain any data step run time executable statements.  If you expect to have array DT or array DATE this is not how to do it.  You will need macro logic to change the code that is compiled by the data step.

data Test2;
   set Test1;
   if COL1='2' then do; *-----------------------> I am looking for this 'if statement' to prevail between the two in this data step......;
   
%let arrelements=%sysevalf(%sysevalf(((&Result/(360/2)),ceil)+1);
    array dt{&arrelements} dt1-dt&arrelements;
   end;
  
else if COL1='12' then do; *--------------> ....instead, this is the one that prevails, completely ignoring the one before it, despite the fact that InterestFrequency=2 (text value).;
   
%let arrelements=%eval(&Result/(360/12));
    array date{&arrelements} date1-date&arrelements;
   end;
run;
Esteemed Advisor
Posts: 7,280

Re: Not sure why this is not working

What is: &FirstCpnDate?

Frequent Contributor
Posts: 120

Re: Not sure why this is not working

Arthur,

That is a typo on my part. Please substitute &FirstCpnDate with &ValuationDate.

Super User
Super User
Posts: 6,307

Re: Not sure why this is not working

You have mixed up macro code and SAS code.

1) The macro variable ARRELEMENTS cannot have two different values at one time.

2) %SYSEVALF() will not understand the text string CEIL.

3) Even if the IF statement worked there are no executable statements in either THEN clause.

What do it want the SAS datastep to DO when COL1='2' ?

Frequent Contributor
Posts: 120

Re: Not sure why this is not working

Tom,

Thanks for getting back to me; your points 1 and 2 are well taken. With regards to your third point, however (also raised by data_null_), I am trying to define one array (array dt{}) if COL1='2' and a different array(array date{}) if COL='12'. Are the array definitions not deemed executable statements within the THEN clauses?

Super User
Super User
Posts: 6,307

Re: Not sure why this is not working

An array statement just makes it easier to reference a series of variables.  If the variables already exist it does nothing. Otherwise all it will do by itself is define them.  Without some type of assignment statement any newly created variables will not have values.

So what is it that you want to happen that is different when COL1='2' ?

Frequent Contributor
Posts: 120

Re: Not sure why this is not working

Tom,

Again, many thanks for your continued insight.

My intentions are best explained in the last data step below (data test3), which is a (cleaner) variation of the code I have above. 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, which is what I am trying to deal with on my other posting ("Converting Character to Date (Number)) on which you have also commented. For your convenience, I repeated the majority of this message in that posting as well.

I hope this clarifies matters; thanks again.

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;

Esteemed Advisor
Posts: 7,280

Re: Not sure why this is not working

I think you are trying to do something like the following:

data WORK.NCDebt;

     infile 'c:\art\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);

  InterestFrequency=strip(InterestFrequency);

  if InterestFrequency='2' then do;

    count1=(intck('semiyear',input(ValuationDate,date9.),input(MaturityDate,date9.))+1);

  end;

  else if InterestFrequency='12' then do;

    count1=(intck('month',input(ValuationDate,date9.),input(MaturityDate,date9.))+1);

  end;

  call symput('arrelements',count1);

  call symput('InterestFrequency',InterestFrequency);

  keep MaturityDate ValuationDate InterestFrequency;

run;

%macro doit;

    %if &InterestFrequency. eq 2 %then %do;

     array dt{&arrelements} dt1-dt%eval(&arrelements.+0);

   %end;

   %else %if &InterestFrequency. eq 12 %then %do;

     array date{&arrelements} date1-date%eval(&arrelements.+0);

   %end;

%mend;

data Test3;

  set Test2;

  %doit

run;

☑ This topic is SOLVED.

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

Discussion stats
  • 33 replies
  • 803 views
  • 7 likes
  • 4 in conversation