Dynamic calculations

Frequent Contributor
Posts: 84

Dynamic calculations

Hello Guys,

I have a dataset like this

 cutomerid _201601 _201602 _201603 _201604 _201605 _201606 _201607 _201608 _201609 _201610 _201611 _201612 _201701 _201702 _201703 101 17 17 11 14 17 15 18 12 15 16 19 10 102 11 18 15 13 14 19 14 12 11 103 19 17 11 105 18 15 15 14 14 10 107 15 15 15 15 15 15 15 15 15 15 15 15 15 15 13 109 15 15 15 15 15 15 15 15 15 15 15 15 15 12

and i want output like this :

 cutomerid _201601 _201602 _201603 _201604 _201605 _201606 _201607 _201608 _201609 _201610 _201611 _201612 _201701 _201702 _201703 Tag 101 17 17 11 14 17 15 18 12 15 16 19 10 1st Trasaction in 3 months 102 11 18 15 13 14 19 14 12 11 1st Trasaction in 6 months 103 19 17 11 1st Trasaction in 12 months 105 18 15 15 14 14 10 1st Trasaction in 9 months 107 15 15 15 15 15 15 15 15 15 15 15 15 15 15 13 Blank 109 15 15 15 15 15 15 15 15 15 15 15 15 15 12 Blank

i want the code should be dynamic o April it will check last 3months 6 months..like that.

Thanks alot.

Posts: 5,541

PG
Contributor
Posts: 54

Re: Dynamic calculations

Why customer 102's tag is "1st Trasaction in 6 months". What is the first transaction and what happed in 6 months
Contributor
Posts: 57

Re: Dynamic calculations

Attaching Solution. Hopefully this should works for you.

data cal_customer(drop=i count);
set customer;
array vars(&tot_month) _201601--_201703;
if vars[14]=. then
do;
count=1;
do i = 13 to 1 by -1;
if vars[i]=. then count=count+1;
end;
end;
else count=0;
if count ne 0 then tag=cats("1st transaction in",count,"months");
else tag="Blank";
run;

Posts: 5,541

Re: Dynamic calculations

Finally managed to paste your data. Here is the code:

``````data have;
infile datalines dsd;
input cutomerid _201601 _201602 _201603 _201604 _201605 _201606 _201607
_201608 _201609 _201610 _201611 _201612 _201701 _201702 _201703;
datalines;
101,17,17,11,14,17,15,18,12,15,16,19, , , ,10
102,11,18,15,13,14,19,14,12, , , , , , ,11
103,19,17, , , , , , , , , , , , ,11
105,18,15,15,14,14, , , , , , , , , ,10
107,15,15,15,15,15,15,15,15,15,15,15,15,15,15,13
109,15,15,15,15,15,15,15,15,15,15, ,15,15,15,12
;

data want;
set have;
array a{*} _:;
length tag \$32;
tag = "blank";
if not missing(a{dim(a)}) then do;
do i = 1 to dim(a) - 1 while (missing(a{dim(a)-i}));
end;
if i > 1 then tag = catx(" ", "1st trasaction in", i-1, "months");
end;
drop i;
run;``````
PG
Frequent Contributor
Posts: 84

Re: Dynamic calculations

Thanks fro the code. Can we dynamically add last 3,6,12 months sum and count in other columns? so next month also same like that..i tried intilize the months in separate arrays and sum but that not dynamic is there any other approach to pick last 3,6 from the one array?

i want ouput like this

 Tag Last_3_months_sum Last_6_months_sum Last_12_months_sum Last_3_months_count Last_6_months_count Last_12_months_count 1st trasaction in 3 months 10 45 136 1 3 9 1st trasaction in 6 months 11 11 83 1 1 6 1st trasaction in 12 months 11 11 11 1 1 1 1st trasaction in 9 months 10 10 38 1 1 3 blank 43 88 178 3 6 12 blank 42 72 162 3 5 11

Thanks so much for your time.

Frequent Contributor
Posts: 84

Re: Dynamic calculations

anyone ther to help me with the code.. Thanks

Discussion stats
• 6 replies
• 219 views
• 3 likes
• 4 in conversation