Hello, I'm struggling to do an incremental count by distinct ID's. Below is an example of my expectation.
Basically, I want to know how many distinct ID's there are by customer (in order of date) between any results of -4. So after that -4 result we begin counting again.
data have; input NAME $ DATE ID RESULT; format DATE yymmdd10.; cards; Joe 20190101 1 500 Joe 20190102 1 500 Joe 20190103 1 500 Joe 20190104 1 -4 Tom 20190104 1 500 Tom 20190101 1 -4 Tom 20190102 1 500 Tom 20190103 2 500 Tom 20190104 3 500 Tom 20190104 7 -4 ; run; data want; input NAME $ DATE ID RESULT want; format DATE yymmdd10.; cards; Joe 20190101 1 500 1 Joe 20190102 1 500 1 Joe 20190103 1 500 1 Joe 20190104 1 -4 1 Tom 20190104 1 500 1 Tom 20190101 1 -4 1 Tom 20190102 1 500 1 Tom 20190103 2 500 2 Tom 20190104 3 500 3 Tom 20190104 7 -4 4 ; run;
See if this works:
data want;
set have;
by name id /* notsorted if id is not ascending */;
retain want;
if first.name or lag(result) = -4 then want = 1;
else if first.id then want + 1;
run;
Untested, posted from my tablet.
See if this works:
data want;
set have;
by name id /* notsorted if id is not ascending */;
retain want;
if first.name or lag(result) = -4 then want = 1;
else if first.id then want + 1;
run;
Untested, posted from my tablet.
This appears to work, still need to test in the larger scheme of things. I was missing the else if in my attempts!
Thanks so much!
Thank you for the response, so this was SUPER close however it's not catching DISTINCT ID's. Below is a better example of this. Currently, if ID 1 appears, then 2 then 1 again it'll increment to 3 when it should only be 2.
data have; input NAME $ Flag DATE ID RESULT; format DATE yymmdd10.; cards; Joe 0 20190101 1 500 Joe 0 20190102 1 500 Joe 0 20190103 1 500 Joe 0 20190104 1 -4 Joe 1 20190121 1 500 Joe 0 20190122 1 500 Tom 0 20190104 1 500 Tom 0 20190101 1 -4 Tom 0 20190102 1 500 Tom 0 20190103 2 500 Tom 0 20190104 3 500 Tom 0 20190105 1 500 Tom 0 20190104 7 -4 ; run; data want; input NAME $ DATE ID RESULT want; format DATE yymmdd10.; cards; Joe 0 20190101 1 500 1 Joe 0 20190102 1 500 1 Joe 0 20190103 1 500 1 Joe 0 20190104 1 -4 1 Joe 1 20190121 1 500 1 Joe 0 20190122 1 500 1 Tom 0 20190104 1 500 1 Tom 0 20190101 1 -4 1 Tom 0 20190102 1 500 1 Tom 0 20190103 2 500 2 Tom 0 20190104 3 500 3 Tom 0 20190105 1 500 3 Tom 0 20190106 2 500 3 Tom 0 20190104 7 -4 1 ; run;
[A new thread has been started to discuss this follow-up question: https://communities.sas.com/t5/SAS-Programming/Increment-on-out-of-order-but-distinct-values/m-p/749...]
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.
Ready to level-up your skills? Choose your own adventure.