topic Re: Count by group using conditions in SAS Programming

Count by group using conditions

kashun — Sun, 08 Nov 2020 19:52:51 GMT

I am trying to do a count of a condition by id but for some reason my counts are not working properly. Need some advise. I have data below:

Have

data have;
input @1 id @5 amount1 @7 amount2;
datalines;
345 70 20
345 30 30
345 80 50
412 30 14
412 70 20
412 20 90
412 30 80
563 40 30
563 50 60
;
run

I want if sum of amount1 and amount2 is greater or equal to 1 then flag+1 by ID

Want

ID	AMOUNT1	AMOUNT2	FLAG
345	70	20	.
345	30	30	.
345	80	50	1
412	30	14	.
412	70	20	.
412	20	90	1
412	30	80	2
563	40	30	.
563	50	60	1
563	40	100	2
563	50	70	3

Re: Count by group using conditions

FreelanceReinh — Sun, 08 Nov 2020 20:35:07 GMT

Hi @kashun,

@kashun wrote:

I want if sum of amount1 and amount2 is greater or equal to 1 then flag+1 by ID

I assume you mean " ... to 100 ..."

data want;
set have;
by id;
if first.id then flag=.;
if amount1+amount2>=100 then flag+1;
run;

It's not clear from your example what should happen to the flag if the sum drops below 100 within a BY group. The code above retains the previous value in this case. If you want FLAG to be missing instead, use a second flag variable like this:

data want(drop=_:);
set have;
by id;
if first.id then _flag=.;
if amount1+amount2>=100 then do;
  _flag+1;
  flag=_flag;
end;
run;

Re: Count by group using conditions

kashun — Sun, 08 Nov 2020 23:43:32 GMT

Thanks very much @FreelanceReinhard.

If sum drops below 100, we should have missing. Looks like your code is working.

Thanks again