Solved
Contributor
Posts: 23

# Creating change variables contd.

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.

Accepted Solutions
Solution
‎01-24-2015 02:54 AM
Super User
Posts: 10,846

## Re: Creating change variables contd.

```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

All Replies
Solution
‎01-24-2015 02:54 AM
Super User
Posts: 10,846

## Re: Creating change variables contd.

```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

Contributor
Posts: 23

## Re: Creating change variables contd.

Thanks so much for your sample code Xia keshan.

🔒 This topic is solved and locked.

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

Discussion stats
• 2 replies
• 351 views
• 0 likes
• 2 in conversation