Count distinct inside period by group

[mm84]

Hi community,

I would like to accumulate a count distinct of a variable for each period inside a group. I tried to use retain with no luck. Can someone point me in the right direction?

data MyDataset1;
length group $1 Period 6 Key $8; 
    infile datalines truncover;
    input group $ Period Key $ ;
    datalines;
A 202212 M0
A 202301 M1
A 202302 M1
A 202302 M2
A 202303 M1
A 202303 M3
B 202301 L1
B 202302 L1
B 202303 L2
B 202303 L3
;
run;

data MyDataset2;
length group $1 Period 6 cummulative 6 ; 
    infile datalines truncover;
    input group $ Period cummulative ;
    datalines;
A 202212 1
A 202301 1
A 202302 2
A 202303 4
B 202301 1
B 202302 1
B 202303 3
;
run;

Thanks

[lavu_90]

Could you please post the your wanted results, then it will helpful for more details

[mm84]

Sorry, what I have is MyDataset1. What I want is MyDataset2. Thanks

[Kurt_Bremser]

Why do you want a result of 4 for the combination A/202303, when there's only two observations in that group?

[mm84]

I don't want to count inside each group, but to accumulate distinct keys from the begging. In 202303 it should count M0, M1, M2, M3. Thanks.

[Kurt_Bremser]

In 2023 there is only M1 and M3.

[mm84]

I need to accumulate from previous periods. For 202303 it should distinct count from all previous periods. That is why in 202303 it should count M0, M1, M2, M3.

[Kurt_Bremser]

And if you want to count all distinct values of key within a group up to that period, then the value for A/202301 should be 2 (M0 and M1).

[mm84]

Sorry, you are right. For 202301 it should be 2.

[Kurt_Bremser · Accepted solution]

Ok, try this:

data want;
set have;
by group period;
length keys $32767;
if first.group then keys = "";
if not findw(keys,key,",","t") then keys = catx(",",keys,key);
if last.period;
cumulative = countw(keys,",");
drop keys;
run;

[mm84]

I am using SAS Enterprise Guide 8.3. When applying the solution it returns:

[s_lassen]

I think you got a count of 1 for all groups because @Kurt_Bremser forgot a retain statement, he probably meant:

data want;
set have;
by group period;
length keys $32767;
retain keys;
if first.group then keys = "";
if not findw(keys,key,",","t") then keys = catx(",",keys,key);
if last.period;
cumulative = countw(keys,",");
drop keys key;
run;

[mm84]

Thanks @s_lassen, it is fix with the missing retain.
I will mark @Kurt_Bremser solution as accepted.

[mm84]

Hi @Kurt_bremsen, thanks for your help. As @l_lanssen notice there was a missing retain to complete the solution.

[andreas_lds]

Assuming that MyDataset2 shows the expected result, it is still not clear why cummulative is 2 in the third observation.