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.
Please repost your input data in a readable (pastable) format.
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;
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;
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.
anyone ther to help me with the code.. Thanks
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.