topic Re: Counting by multiple groups with conditions in SAS Programming

Counting by multiple groups with conditions

kashun — Wed, 23 Dec 2020 21:01:55 GMT

Hello,

I have a data which I would like to crate a flag that counts by condition that the about is greater or equal to 300. Else, it should provide a missing

Tried using notsorted with Flag +1 and hash object but keeps not getting what I want. Please advise.

Have

data have;
input @1 ID1 @5 GROUP @7 Amount;
datalines;
342 0 250
342 0 270
342 0 300
342 1 100
342 1 120
342 1 170
342 0 350
342 0 380
342 1 190
546 1 200
546 0 400
546 0 440
546 0 480
546 0 550
546 0 600
546 0 620
546 1 250
546 0 670
546 1 320
546 0 690
546 1 350
546 1 360
546 1 380
546 0 .
546 0 700
546 0 730
484 0 .
484 0 100
484 0 300
484 0 320
484 0 340
484 0 370
484 0 390
 484 0 400
;
run;

Want

ID1	GROUP	Amount	Flag
342	0	250	.
342	0	270	.
342	0	300	1
342	1	100	.
342	1	120	.
342	1	170	.
342	0	350	2
342	0	380	3
342	1	190	.
546	1	200	.
546	0	400	1
546	0	440	2
546	0	480	3
546	0	550	4
546	0	600	5
546	0	620	6
546	1	250	.
546	0	670	7
546	1	320	1
546	0	690	8
546	1	350	2
546	1	360	3
546	1	380	4
546	0	.	.
546	0	700	9
546	0	730	10
484	0	.	.
484	0	100	.
484	0	300	1
484	0	320	2
484	0	340	3
484	0	370	4
484	0	390	5
484	0	400	6

Re: Counting by multiple groups with conditions

Kurt_Bremser — Wed, 23 Dec 2020 20:11:41 GMT

Is the number of different group values known beforehand?

Re: Counting by multiple groups with conditions

novinosrin — Wed, 23 Dec 2020 20:21:26 GMT

Hi @kashun Can you please review your results as you seem to continue counting even after change of ID's in the example 342-546 however you seem to be resetting back to 1 in 484??

Re: Counting by multiple groups with conditions

kashun — Wed, 23 Dec 2020 21:02:56 GMT

@novinosrin. Thanks very much. I fixed that.

Re: Counting by multiple groups with conditions

novinosrin — Wed, 23 Dec 2020 21:04:38 GMT

Thank you @kashun . Here you go-


data have;
input @1 ID1 @5 GROUP @7 Amount;
datalines;
342 0 250
342 0 270
342 0 300
342 1 100
342 1 120
342 1 170
342 0 350
342 0 380
342 1 190
546 1 200
546 0 400
546 0 440
546 0 480
546 0 550
546 0 600
546 0 620
546 1 250
546 0 670
546 1 320
546 0 690
546 1 350
546 1 360
546 1 380
546 0 .
546 0 700
546 0 730
484 0 .
484 0 100
484 0 300
484 0 320
484 0 340
484 0 370
484 0 390
 484 0 400
;
run;

data want;
if _n_=1 then do;
  dcl hash h(suminc:'_iorc_');
  h.definekey('id1','group');
  h.definedone();
end;
set have;
retain _iorc_ 1;
if amount ge 300 then do;
 h.ref();
 h.sum(sum:Flag);
end;
run;

Re: Counting by multiple groups with conditions

kashun — Wed, 23 Dec 2020 21:28:39 GMT

Works like a charm @novinosrin. Thanks very much.