SAS Placeholder value

Question

I am looking to have a flexible importing structure into my SAS code. The import table from excel looks like this:

data have;
input Fixed_or_Floating $ asset_or_liability $ Base_rate_new;
datalines;

FIX A 10
FIX L Average Maturity
FLT A 20
FLT L Average Maturity
;
run;

The original dataset I'm working with looks like this:

data have2;
input ID Fixed_or_Floating $ asset_or_liability $ Base_rate;
datalines;

1 FIX A 10
2 FIX L 20
3 FIX A 30
4 FLT A 40
5 FLT L 30
6 FLT A 20
7 FIX L 10
;
run;

The placeholder "Average Maturity" exists in the excel file only when the new interest rate is determined by the average maturity of the bond. I have a separate function for this which allows me to search for and then left join the new base rate depending on the closest interest rate. An example of this is such that if the maturity of the bond is in 10 years, i'll use a 10 year interest rate.

So my question is, how can I perform a simple merge, using similar code to this:

proc sort data = have;
by fixed_or_floating asset_or_liability;
run;

proc sort data = have2;
by fixed_or_floating asset_or_liability;
run;

data have3 (drop = base_rate);
merge have2 (in = a)
      have1 (in = b);
by fixed_or_floating asset_or_liability;

run;

The problem at the moment is that my placeholder value doesn't read in and I need it to be a word as this is how the excel works in its lookup table - then I use an if statement such as

if base_rate_new = "Average Maturity" then do;

(Insert existing Function Here)

end;

so just the importing of the excel with a placeholder function please and thank you.

TIA.

Answer 1

I'm not 100% sure if this behaviour corresponds with how your data appears once you import it from excel but if I run your code to create have I get:

NOTE: Invalid data for Base_rate_new in line 145 7-13.
RULE:      ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+--
145        FIX L Average Maturity
Fixed_or_Floating=FIX asset_or_liability=L Base_rate_new=. _ERROR_=1 _N_=2
NOTE: Invalid data for Base_rate_new in line 147 7-13.
147        FLT L Average Maturity
Fixed_or_Floating=FLT asset_or_liability=L Base_rate_new=. _ERROR_=1 _N_=4
NOTE: SAS went to a new line when INPUT statement reached past the end of a line.
NOTE: The data set WORK.HAVE has 4 observations and 3 variables.

Basically it's saying that when you tried to import the character strings as numeric it couldn't do it so it left them as null values. If we print the table we can see the null values:

proc print data=have;
run;

Result:

Fixed_or_    asset_or_      Base_
Floating     liability    rate_new

   FIX           A           10
   FIX           L            .
   FLT           A           20
   FLT           L            .

Assuming this truly is what your data looks like then we can use the coalesce function to achieve your goal.

data have3 (drop = base_rate);
  merge have2 (in = a)
        have (in = b);
  by fixed_or_floating asset_or_liability;
  base_rate_new = coalesce(base_rate_new,base_rate);
run;

The result of doing this gives us this table:

       Fixed_or_    asset_or_      Base_
 ID    Floating     liability    rate_new

  1       FIX           A           10
  3       FIX           A           10
  2       FIX           L           20
  7       FIX           L           20
  4       FLT           A           20
  6       FLT           A           20
  5       FLT           L           30

The coalesce function basically returns the first non-null value it can find in the parameters you pass to it. So when base_rate_new already has a value it uses that, and if it doesn't it uses the base_rate field instead.