I would need to valorise columns in SAS according to a specific condition of other columns. Can anyone help me?

user_11713727 0
Viewed 32

I need help with a SAS code.

My table displays several contracts. There is a column Expiring_Month, telling on which month the contract expires.

Plus, I have 4 variables Amount_N, Amount_R, Amount_RT, Amount_NT (each of these is divided for 12 months (so 48 columns), so they look like Amount_202001_N, Amount_202002_N, Amount_202003_N and so on till December). Moreover, there are other 4 columns Amount_MIN_N, Amount_MIN_R, Amount_MIN_RT, Amount_MIN_NT.

Basically, Amount_Min_N exists only if Amount_Min_R does not exist (and viceversa), while, Amount_2020XX_RT and Amount_2020XX_NT are valorised until the month before the Expiring_Month.

What I need to do is to give the value of Amount_Min_RT and Amount_Min_NT to the columns Amount_2020XX_R (i should actually give values for 9 (Sep) 10 (Oct), 11 (Nov) 12 (Dec) as I have already data till August).

The first step I have done is the following (I only post Oct valorization, but i did for Nov and Dec):

data xox_sa_1;
set xox_sa;
    *october;
    if (amount_min_n > 0 and amount_min_r = .) then amount_202010_N = amount_min_n;
    if (amount_min_n = . and amount_min_r > 0) then amount_202010_r = amount_min_r;
run;

Then, if for example there is a contract expired on May, the Amount_202005_RT (or Amount_202005_NT, it depends on the case) does not get valorised, but the variable Amount_202005_R (and the following Amount_202006_R, and so on) should get valorised with the Amount_Min_RT/Amount_Min_NT values.

I hope I made it clear ... does anyone have any idea?? I would need to take into consideration the expiring month, but I do not know how.

sas if-statement
1 Answers

You've got a few things going on here, but the general approach you need to understand is the array.

First, though, let's consider the variable naming scheme. amount is constant, so that's fine. Then after that you have two variable portions: 202010 and _n, where the former changes with the month and the latter changes based on what information the variable is describing.

Usually, it would be better to structure this as amount_n_202010 amount_nr_202012 etc., because this allows you to write your arrays more simply, and allows you to easily get at some information using automatic lists. SAS will let you say "all the variables that start with this prefix"; but it won't let you say (as easily) "all the variables with anything between these two other text strings". So I would suggest you organize your variable names that way. The only exception to this is if you more often wanted to say 'all of the 202010 variables' than 'all of the amount_n variables', but I don't think that's the case at least in this example.

Then, the main issue: how do you do the kind of work you're doing. I can't give you code to do what you're doing exactly - and I shouldn't, regardless - because you aren't sufficiently specific in your post; but that's fine, because what you're doing is so common that the idea is easy to explain.

You want to set up four arrays. One each for the four variable types. Then, you do action based on if condition to the correct iteration of the array.

data want;
  set have;
  array amountn  amount_n_202001  - amount_n_202012;
  array amountr  amount_r_202001  - amount_r_202012;
  array amountnt amount_nt_202001 - amount_nt_202012;
  array amountrt amount_rt_202001 - amount_rt_202012;
  
  do _i = 1 to dim(amountn);
    if *condition related to expiration_month* then do;
      amountn[_i] = amount_n;
      amountr[_i] = amount_r;
      ... etc.
    end;
  end;
run;

You'll have to do a few things, and need a few tools for that.

First off, you'll need an easy way to see 'what month are we on right now', since our iteration is 1 through 12 and the month has the whole 2020 bit added to it. You can do this two ways. Either parse the expiration month to get just the month value, and then use that; or use vname() function to find out what month we're currently on. The latter is more helpful if you don't always want to have exactly 12 months; you said you only are doing this september - december for example, so maybe you want to only have those 4 variables, and you don't want to create an array with 12 items.

Here's an example of that. I also assume that expiration_month is a character variable of format 202012 - if it's not, then you'll need to convert it, or convert the variable name pieces to match it.

You can see how these values work here:

  data want;  
      array amountn  amount_n_202001  - amount_n_202012;
      array amountr  amount_r_202001  - amount_r_202012;
      array amountnt amount_nt_202001 - amount_nt_202012;
      array amountrt amount_rt_202001 - amount_rt_202012;
      
      do _i = 1 to dim(amountn);
         month= scan(vname(amountn[_i]),3,'_');         
         put month=;
      end;
    run;

Hopefully that gives you a start - you can work with that structure to then figure out what to apply. You shouldn't need to do 4 or 12 or whatever versions of this code; just one, iterating over the array.

user_16230070
Related