topic Re: Count distinct inside period by group in SAS Programming

Count distinct inside period by group

mm84 — Tue, 12 Dec 2023 10:51:24 GMT

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

Re: Count distinct inside period by group

lavu_90 — Tue, 12 Dec 2023 11:33:58 GMT

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

Re: Count distinct inside period by group

Kurt_Bremser — Tue, 12 Dec 2023 11:42:17 GMT

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

Re: Count distinct inside period by group

mm84 — Tue, 12 Dec 2023 11:42:28 GMT

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

Re: Count distinct inside period by group

mm84 — Tue, 12 Dec 2023 11:45:12 GMT

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.

Re: Count distinct inside period by group

Kurt_Bremser — Tue, 12 Dec 2023 11:46:25 GMT

In 2023 there is only M1 and M3.

Re: Count distinct inside period by group

Kurt_Bremser — Tue, 12 Dec 2023 11:54:05 GMT

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

Re: Count distinct inside period by group

andreas_lds — Tue, 12 Dec 2023 11:54:07 GMT

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

Re: Count distinct inside period by group

mm84 — Tue, 12 Dec 2023 12:01:06 GMT

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.

Re: Count distinct inside period by group

mm84 — Tue, 12 Dec 2023 12:03:03 GMT

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

Re: Count distinct inside period by group

mm84 — Tue, 12 Dec 2023 12:05:15 GMT

Third observation for group A and period 202302, it should count M0, M1, that are the distinct keys until this month.

Re: Count distinct inside period by group

Kurt_Bremser — Tue, 12 Dec 2023 12:20:54 GMT

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;

Re: Count distinct inside period by group

mm84 — Tue, 12 Dec 2023 12:37:38 GMT

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

Re: Count distinct inside period by group

s_lassen — Tue, 12 Dec 2023 13:04:33 GMT

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;

Re: Count distinct inside period by group

mm84 — Tue, 12 Dec 2023 14:12:31 GMT

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

Re: Count distinct inside period by group

mm84 — Tue, 12 Dec 2023 14:14:15 GMT

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