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.
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
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
Thanks so much for your sample code Xia keshan.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.