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
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;
Could you please post the your wanted results, then it will helpful for more details
Why do you want a result of 4 for the combination A/202303, when there's only two observations in that group?
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).
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;
I am using SAS Enterprise Guide 8.3. When applying the solution it returns:
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;
Assuming that MyDataset2 shows the expected result, it is still not clear why cummulative is 2 in the third observation.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.