topic Re: I need help with taking the actual count for sales in SAS Programming

I need help with taking the actual count for sales

Newbie_23 — Tue, 07 Mar 2023 17:53:52 GMT

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

Re: I need help with taking the actual count for sales

ballardw — Tue, 07 Mar 2023 18:58:44 GMT

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

Re: I need help with taking the actual count for sales

Newbie_23 — Tue, 07 Mar 2023 20:12:13 GMT

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

Re: I need help with taking the actual count for sales

ballardw — Tue, 07 Mar 2023 21:41:13 GMT

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

Re: I need help with taking the actual count for sales

Newbie_23 — Tue, 07 Mar 2023 21:57:19 GMT

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.

Re: I need help with taking the actual count for sales

ballardw — Tue, 07 Mar 2023 22:14:24 GMT

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

Re: I need help with taking the actual count for sales

Newbie_23 — Tue, 07 Mar 2023 22:27:39 GMT

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