Distinct Calculation

[MJM11111]

Good day.

I want to create a formula in SAS Visual Analytics, based on the entries as shown below, I want to only sum the 1st entry per Ref number. for example, in the below table we are only looking at Fraud type 4. Suspicious, currently the total rand value is 3 636 910.90 but it should be 1 818 455.45 if I should only calculate based on the 1st line entry.

Fraud Type Fraud Documents Suspicious Avg Principle Debt
4. Suspicious 4.1.1 Suspicious 290 727.49
4. Suspicious 4.1.2 False Employer 290 727.49
4. Suspicious 4.1.2 False Employer 541 537.48
4. Suspicious 4.1.1 Suspicious 541 537.48
4. Suspicious 4.1.1 Suspicious 501 207.50
4. Suspicious 4.1.2 False Employer 501 207.50
4. Suspicious 4.1.2 False Employer 193 649.49
4. Suspicious 4.1.1 Suspicious 193 649.49
4. Suspicious 4.1.1 Suspicious 291 333.49
4. Suspicious 4.1.2 False Employer 291 333.49

[ballardw · Accepted solution]

What is a "ref number"? I don't see anything in what looks like example data that is called "ref number". So as a very minimum you need to describe how we are to recognize a "ref number" and then describe how we are to tell what the "first entry per Ref number" might be.

Very likely this will involve two steps. First adding a calculated value that indicates it is the "first entry per Ref number". Then use that calculated variable to be part of a grouping.

[MJM11111]

Thank you for the response. I have already found the solution .

[PaigeMiller]

---

@MJM11111 wrote:
Thank you for the response. I have already found the solution .

---

@MJM11111

please share the solution with us. We would like to know. And it might help others in the same or similar situation.

[MJM11111]

Thank you for your response, but i already figured it out.

[DataCruncherX]

I faced a similar issue while working with data for my Stardew Valley APK site—making sure only unique user sessions were counted. Hope this helps!