I need help with taking the actual count for sales

[Newbie_23]

Hello,

I have a table as shown below, the customer_ID is unique, and each sale ID is associated with the customer_ID. Based on the sale ID for each customer , i need to look at the highest seq_no for each Sale_ID and account for the units actually sold based on the status for the last highest sequence number for each sales_ID.  For example for customer_ID "1", there was a sale done on 1/1/23 all mapping to Sales_ID "123", but when looking at the status, the actual units sold based on the highest Seq_no and status  set to "Paid", the actual units sold were 3.

I need to code this logic , please help.

If the last seq_no for the sales_ID is paid, then count the value, else if rejected or reversed, then it is counted as zero units.

Sales_ID	Seq_No	Status	Customer_ID	Sale_ date	Units_sales
123	0	Paid	1	1/1/2023	2
123	1	Rejected	1	1/1/2023	2
123	2	Paid	1	1/1/2023	3
155	0	Paid	1	2/1/2023	5
212	0	Paid	2	2/5/2023	1
212	1	Reversed	2	2/5/2023	1
215	0	Rejected	3	2/5/2023	5

[ballardw]

Hint: Show an expected result for example data.

Best is to provide example data as working data step code, barring that open a text window using the </> and paste plain text of the values. Some of the software that pastes tables are next to impossible to work with.

Working data step for your example looks like this:

data have;
   input Sales_ID $	Seq_No	Status $	Customer_ID $	Sale_date :mmddyy10.	Units_sales;
   format sale_date mmddyy10.;
datalines;
123	0	Paid	1	1/1/2023	2
123	1	Rejected	1	1/1/2023	2
123	2	Paid	1	1/1/2023	3
155	0	Paid	1	2/1/2023	5
212	0	Paid	2	2/5/2023	1
212	1	Reversed	2	2/5/2023	1
215	0	Rejected	3	2/5/2023	5
;

One of the reasons we suggest you provide code is that we have to make guesses about displayed values that may not work with your actual data. For example, I assume ID values are character as you seldom do things like "add customer id numbers". I assumed that the date value is in month day year order because it is not possible to tell which is month or day from the example.

I suspect you are missing some rules, such as what impact the Rejected or Reversed status has your "count".

[Newbie_23]

Hello,
Your datatype assumption is correct. Only the status set to paid should be accounted for the count. If status set to rejected or reversed then the count is zero

[ballardw]

---

@Newbie_23 wrote:
Hello,
Your datatype assumption is correct. Only the status set to paid should be accounted for the count. If status set to rejected or reversed then the count is zero

---

I think this is another case of the user knowing a whole lot more than we do and is not quite able to state the conditions because it is obvious. I asked about showing a result because you have this data:

212	0	Paid	2	2/5/2023	1
212	1	Reversed	2	2/5/2023	1

What is the actual "count" resulting here?

[Newbie_23]

Hello,
Sorry i did not state the conditions.
The highest Seq_No number for the same Sales_ID - If Status is set to paid then take the count as count shown under Units_sales ( maybe add additional column as "actual units sold" and show the count) else If status is rejected or reversed, then the actual units sold should be shown as zero.

[ballardw]

---

@Newbie_23 wrote:
Hello,
Sorry i did not state the conditions.
The highest Seq_No number for the same Sales_ID - If Status is set to paid then take the count as count shown under Units_sales ( maybe add additional column as "actual units sold" and show the count) else If status is rejected or reversed, then the actual units sold should be shown as zero.

---

And what if the number in Rejected or Reversed is not the same as for Paid? Hypothetical

212	0	Paid	  2	2/5/2023	5
212	1	Reversed  2	2/5/2023	1

And you should show what you expect the resulting output should show. Show, NOT describe. Does the date need to be included? The sequence number?

[Newbie_23]

For the data that you have questioned,
212 0 Paid 2 2/5/2023 5
212 1 Reversed 2 2/5/2023 1

The max seq_no for sale_ID 212 is 1 and the status is set to reversed, in this case the count for the derived column ( actual_units_sold) should be zero