DATA Step, Macro, Functions and more

Dynamic calculations

Reply
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.

Respected Advisor
Posts: 4,920

Re: Dynamic calculations

Please repost your input data in a readable (pastable) format.

PG
Contributor
Posts: 54

Re: Dynamic calculations

Could you please give more detail about logic.as i understand you want to add "tag" column.
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;

Respected Advisor
Posts: 4,920

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

Ask a Question
Discussion stats
  • 6 replies
  • 170 views
  • 3 likes
  • 4 in conversation