Creating change variables contd.

Dil — Sat, 24 Jan 2015 05:24:48 GMT

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.

Re: Creating change variables contd.

Ksharp — Sat, 24 Jan 2015 07:54:28 GMT

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

Re: Creating change variables contd.

Dil — Sun, 25 Jan 2015 21:10:28 GMT

Thanks so much for your sample code Xia keshan.

topic Re: Creating change variables contd. in SAS Enterprise Guide

Creating change variables contd.

Re: Creating change variables contd.

Re: Creating change variables contd.