Creating change variables contd.

[Dil]

Hi all,

Following on my previous questions on creating change variables, I have another variant of variables. Below are the sample data set. There are variables EVP_mmmyyyy which has 1s or missing (only from EVP_DEC2012 to EVP_DEC2014). What I want to do is similar to before. I want to create two change variables.

a.	EVP_hits_12mons:  sum of EVP_MMMYYYY in the last 12 months based on EVAL_BEFORE_DATE, with DEC2012 being the earliest.
b.	EVP_since_Dec12:  sum of EVP_MMMYYY since DEC2012 to month before EVAL_BEFORE_DATE

sample dataset:

EVAL_BEFORE_DATE      EVP_DEC2012       EVP_JAN2013 --EVP_DEC2013         EVP_JAN2014--EVP_DEC2014

01-FEB-2013                          1                            .                               1                       1                       .           

20-DEC-2011                           .                             1                              .                        .                        1         

26-SEP-2010                           .                            .                               .                        .                        .      

01-JAN-2013                          1                            .                               1                       1                       .      

sample output:

EVAL_BEFORE_DATE      EVP_DEC2012       EVP_JAN2013 --EVP_DEC2013         EVP_JAN2014--EVP_DEC2014     EVP_hits_12mons     EVP_since_Dec12

01-FEB-2013                          1                            .                               1                       1                       .                            1                                1

20-DEC-2011                           .                             1                              .                        .                        1                          0                                0

26-SEP-2010                           .                            .                               .                        .                        .                            0                                0

01-JAN-2013                          1                            .                               1                       1                       .                             1                                1

Following Reeza's suggestion using an array with VvalueX and the INTNX function, below are my code:

data set want (drop=v: i var var1);

  set have;

   array l_12(12) v1 v2 v3 v4 v5 v6 v7 v8 v9 v10 v11 v12;

  do i=-12 to -1;

  var=put(intnx('month', EVAL_BEFORE_DATE, i), monyy7.);

  var1=catx("_","EVP",var);

  l_12(i+13)=vvaluex(var1);

  end;

  *EVP_hits_12mons;

  EVP_hits_12mons=sum(of l_12(*));

  if EVP_hits_12mons = . then EVP_hits_12mons = 0;

  *EVP_since_Dec12;

  if eval_before_date < '31-DEC-2012'd then EVP_since_Dec12=0;

  else EVP_since_Dec12=sum(of EVP_DEC2012--var1);

run;

My first problem is that EVP_hits_12mons is giving me reasonable outputs. However, is there a better way to get the wanted result instead of using if EVP_hits_12mons = . then EVP_hits_12mons = 0; for those ones who are all missing?

My second problem is that EVP_since_Dec12 is giving me wrong output. I suspect, the way I specify EVP_DEC2012--var1 is not correct? I also tried to specify EVP_DEC2012--l_12(12) but it gave me errors.

Any suggestions would be greatly appreciated.

[Ksharp · Accepted solution]

data have;
input EVAL_BEFORE_DATE : date11.     EVP_DEC2012       EVP_JAN2013  EVP_DEC2013         EVP_JAN2014 EVP_DEC2014 ;
format      EVAL_BEFORE_DATE date11.;
cards;
01-FEB-2013                          1                            .                               1                       1                       .           
20-DEC-2011                           .                             1                              .                        .                        1         
26-SEP-2010                           .                            .                               .                        .                        .      
01-JAN-2013                          1                            .                               1                       1                       .      
;
run;
data want ;
 set have;
 array x{*} EVP_:;
 array y{9999} $ 20 _temporary_;
call missing(of y{*});n=0;EVP_hits_12mons=0;
 do i=-12 to 0;
  n+1;y{n}=cats('EVP_',put(intnx('month',EVAL_BEFORE_DATE,i),monyy7.));
 end;
 do j=1 to dim(x);
  if upcase(vname(x{j})) in y then  EVP_hits_12mons+x{j};
 end;

call missing(of y{*});n=0;EVP_since_Dec12=0;
 do i=0 to intck('month','01DEC2012'd,EVAL_BEFORE_DATE);
  n+1;y{n}=cats('EVP_',put(intnx('month','01DEC2012'd,i),monyy7.));
 end;
 do j=1 to dim(x);
  if upcase(vname(x{j})) in y then  EVP_since_Dec12+x{j};
 end;
 drop i j n;
run;
 

Xia Keshan

[Dil]

Thanks so much for your sample code Xia keshan.