topic Re: Creating a counter with multiple sort columns and conditions in SAS Programming

Creating a counter with multiple sort columns and conditions

Arch3nar — Mon, 20 May 2024 22:49:19 GMT

Hi Team,

I am trying to created a counter (as per the expected results below).
The data is sorted by Category Snapshot Focus_Date
The rule is that expected result (a counter), where the focus_date is present is the difference in months, This I can do with an INTNX function.
For the counter: When a new first.category arrives and the focus_date is blank I'd like the counter restarted at 0. If the next record is for the same category I'd like it to increase by 1.
If for the same category a new focus_date is found, I can use the intnx function. However, then I'd like to restart the counter at 0 if there is no focus_date in the next line.

If there have been no historic focus_date items yet, then the result should also be 0..

This is the code I tried without incorporating the intnx:

data unique_months_2;
set unique_months;
by category snapshot focus_date;
if first.category or lag(focus_date) <> focus_date then expected_result = 0;
expected_result +1;
run;

My sample.

Snapshot	Category	Focus_Date	Expected Result
31-Mar-22	1	15-May-22	-2
30-Apr-22	1	15-May-22	-1
31-May-22	1		0
30-Jun-22	1		1
31-Jul-22	1	15-Jul-24	-24
31-Jan-23	2		0
28-Feb-23	2	15-Feb-23	-1
31-Mar-23	2		0
30-Apr-23	2		1
31-Aug-21	3		0
30-Sep-21	3		0
31-Oct-21	3		0
31-Mar-21	6	15-Mar-21	-1
30-Apr-21	6		0
31-May-21	6	20-May-21	-1
30-Jun-21	6		0
31-Oct-22	7		0
30-Nov-22	7		0
31-Dec-22	7		0
31-Dec-23	10	15-Jan-24	-2
31-Jan-24	10	15-Jan-24	-1
29-Feb-24	10		0
31-Mar-24	10		1
30-Apr-24	10	17-Apr-29	-60

Re: Creating a counter with multiple sort columns and conditions

Patrick — Tue, 21 May 2024 00:59:26 GMT

According to your rules why is the expected result -1 in below two rows in red?

Snapshot	Category	Focus_Date	Expected Result
31-Jan-23	2		0
28-Feb-23	2	15-Feb-23	-1
31-Mar-23	2		0
...	...		....
31-Mar-21	6	15-Mar-21	-1
30-Apr-21	6		0

Re: Creating a counter with multiple sort columns and conditions

Arch3nar — Tue, 21 May 2024 01:14:13 GMT

Apologies - there is a lot going on here. I am probably better taking a subset of the code to add a counter to, Even then I'm struggling. But to answer your question: The line following the red are 0. It counts backwards. However, I can update that with a INTNX formula. I'm not so concerned about these. It is more the increasing counts that I am concerned about.

Re: Creating a counter with multiple sort columns and conditions

Patrick — Tue, 21 May 2024 02:14:53 GMT

@Arch3nar

I or someone else can certainly help you with the code once we understand the rules.

That you provided sample data with the expected result is very helpful. My challenge right now is that I can't match the rules you formulated with the sample data you provided and I also couldn't figure out what the rules might be just based on the sample data. That's why I'm asking clarification questions. Well... may-be someone else will understand.