topic Re: Get count of records from previous month and put it next to current month in SAS Programming

Get count of records from previous month and put it next to current month

SAS_New_User1 — Thu, 14 Sep 2023 17:09:50 GMT

I want to count records from previous month and add it next to current month I.e. display all this information in a table.

Id CurrentMonth Previousmonth currentCount previouscount

1 Jan Dec 10 15
2 Feb Jan 7 10
3. Mar Feb 2 7
4 Apr Mar 12 2

Thank you

Re: Get count of records from previous month and put it next to current month

ballardw — Thu, 14 Sep 2023 17:19:28 GMT

Show what you currently have.

Then show, using that example, what you expect as a result. Your current example doesn't show anyplace to get 15 for the first line.

Caution: Previous month when there is no YEAR value is very problematic. If you only have "JAN" and "DEC" in your data how do you know which "DEC" come before "JAN"??

Re: Get count of records from previous month and put it next to current month

Reeza — Thu, 14 Sep 2023 17:19:53 GMT

Does your data have a year component?

Re: Get count of records from previous month and put it next to current month

SAS_New_User1 — Thu, 14 Sep 2023 17:34:09 GMT

The year is 2023 for all the months the data starts from Jan 1 2023 and for December the record count can be considered 0 according to query.

I have already produced count of transactions in each month so my original table has

Cust_id Month Txn_count
1 Jan 15
1 Feb 5
1 May 12
1 June 4
2 Sept 64
2 Dec 6
3 Aug 34
3 Sept 2
3 Oct 23

I want to show like this
Cust_id Month_current. Txn_count_current Month_previous Txn_count_previous
1 Jan 15 Dec 0
1 Feb 5 Jan 15
1 May 12 Apr 0
1 June 4 May 12
2 Sept 64 Aug 0
2 Dec 6 Nov 0
3 Aug 34 Jul 0
3 Sept 2 Aug 34
3 Oct 23 sept 2

Re: Get count of records from previous month and put it next to current month

SAS_New_User1 — Thu, 14 Sep 2023 17:37:25 GMT

The year is all 2023 so year can be ignored

Re: Get count of records from previous month and put it next to current month

SAS_New_User1 — Thu, 14 Sep 2023 18:02:34 GMT

Re: Get count of records from previous month and put it next to current month

ballardw — Thu, 14 Sep 2023 20:43:04 GMT

IF your data is all one year then there is no "previous month" for Jan.

Months as 3-letter abbreviations are not easy to work with in general, even worse they aren't even all the same length. They do not sort. You have to explicitly describe rules for what is prior, or following, AND you need an extra rule for every interval, 2 months prior or 2 months after that may be needed.

Suggestion is create an actual DATE because SAS provides tools to examine dates, increment and display.

First is provide example data in the form or a working data step. I suspect this has been mentioned before. Something like:

data have;
   input Cust_id Month $ Txn_count;
datalines;
1 Jan 15
1 Feb 5
1 May 12
1 June 4
2 Sept 64
2 Dec 6
3 Aug 34
3 Sept 2
3 Oct 23
;

With that then we have one possible solution (there are others)

data want;
   set have;
   by cust_id notsorted;
   datevar = input(cats('01',substr(month,1,3),'2023'),date9.);
   format datevar monname3.;
   month_previous = intnx('month',datevar,-1,'b');
   format month_previous monname3.;
   txn_count_previous = lag(txn_count);
   if first.cust_id then txn_count_previous = 0;
run;

We create a SAS date value by creating a string that is in the form of ddMONyyyy, or 01JAN2023 and read that with an informat designed to do such. Note: SEPT is right out (why? only month with 4 characters).

The format statement makes the default appearance 3 letters such as Jan. With that value in hand we can use the SAS INTNX function to increment the value, in this case by month, -1 is "previous", and 'b' says beginning of the month.

Noticing that you changed the description of the problem again by introducing custid as variable to consider as a group, meaning the count assignment I add the BY Cust_Id. When you process data in a data step SAS provides automatic variables to indicate whether a particular observation is from the first or last record of a group. The variables accessed using First.variablename or Last.variablename and have values of 1 (for Yes, is first or is last) or 0. So we can conditionally do things at the start or the end of the group. The NOTSORTED means treat the values as group but may not be actual sort order as the BY statement expects without the option.

The LAG function gets the value from the previous record. Conditionally we assign the desired value of 0 when the first of the cust_id values is reached.

I left the new Datevar in the data in case you need to do anything that uses "month" in actual month order.

Re: Get count of records from previous month and put it next to current month

mkeintz — Fri, 15 Sep 2023 00:08:04 GMT

you really want to avoid generating data variables,
and
you know that you never have a gap of more than 12 months for a given cust_id

then
you can utilize a two-dimensional array, the first row for Jan through Dec abbreviations, and the second row for Dec through Jan. I.e. the second row names months that precede the months present in the corresponding column of the first row:

data want;
  set have;
  by cust_id;
  array mon_list {2,12} $3
    _temporary_ ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec',
                 'Dec','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov');
  cur_month=propcase(substr(month,1,3));
  prv_month=mon_list{2,whichc(cur_month,of mon_list{*})};

  txn_count_previous = ifn(lag(cur_month)=prv_month and first.cust_id=0,lag(txn_count),0);

run;