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 |
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;
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;
Thanks very much @FreelanceReinhard.
If sum drops below 100, we should have missing. Looks like your code is working.
Thanks again
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.