BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
MJM11111
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

5 REPLIES 5
ballardw
Super User

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
Calcite | Level 5
Thank you for the response. I have already found the solution .
PaigeMiller
Diamond | Level 26

@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.

--
Paige Miller
MJM11111
Calcite | Level 5
Thank you for your response, but i already figured it out.
DataCruncherX
Fluorite | Level 6

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!