topic Re: Dynamic calculations in SAS Programming

Dynamic calculations

kumarK — Wed, 22 Mar 2017 18:33:52 GMT

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.

Re: Dynamic calculations

PGStats — Wed, 22 Mar 2017 19:00:39 GMT

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

Re: Dynamic calculations

Yavuz — Wed, 22 Mar 2017 19:14:11 GMT

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

Re: Dynamic calculations

lakshmi_74 — Thu, 23 Mar 2017 01:49:30 GMT

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;

Re: Dynamic calculations

PGStats — Thu, 23 Mar 2017 02:59:12 GMT

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;

Re: Dynamic calculations

kumarK — Thu, 23 Mar 2017 05:30:22 GMT

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.

Re: Dynamic calculations

kumarK — Sat, 25 Mar 2017 19:10:45 GMT

anyone ther to help me with the code.. Thanks