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 |
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;
Is the number of different group values known beforehand?
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??
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.