Multi-line transactional data advice please

[Mark Webb-5]

I have transactional data where each transaction is a line. Unique customers are identifiable [ClientID].
I have the month of the transaction [TrMonth].
In the example below the data is sorted by ClientID and then TrMonth.
I want to create/compute the 2 columns FLLabel [identifying the First and Last transactions] and TRCount [Counting the transactions] as shown below.
I'm so used to single line data - find this a bit alien - have a feeling the solution is in the LAG command but need so advice please.

Sort 1      Sort 2
ClientID   TrMonth   FLLabel   TRCount
1              Jan          First         1
1              Feb          x              2
1              Mar         Last          3
2              Feb         First          1
2              Mar         Last          2
3              Mar         First          1
3              Apr          x               2
3              July         x               3
3              Sept        x               4
3              Nov         Last          5
etc.

Any guidance will be appreciated.
Regards
Mark
     

     
===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

[Andy W]

First and last can be identified via MATCH FILES, e.g.

SORT CASES BY ClientID TrMonth.
MATCH FILES FILE = *
  /FIRST = FirstFlag
  /LAST = LastFlag
  /BY ClientID.

And FirstFlag and LastFlag will be dummy variables. From there you can create your own combined variable.

The other column, creating an order counter within a variable, is a FAQ. I wrote a blog post showing one way to do it using LAG, https://andrewpwheeler.wordpress.com/2013/02/18/using-sequential-case-processing-for-data-management-in-spss/. David Marso in the comments shows another way to do it using SPLIT FILE and CREATE in the comments.

[PRogman]

Sorting on TrMonth may give an alphabetic order...
I would suggest a recode  before matching, and then RANK :

RECODE TrMonth ('Jan'=1) ('Feb'=2) ('Mar'=3) ('Apr'= 4) ('May'= 5) ('Jun'= 6)
               ('Jul'=7) ('Aug'=8) ('Sep'=9) ('Oct'=10) ('Nov'=11) ('Dec'=12) INTO MM.

SORT CASES BY ClientID MM.

MATCH FILES
  FILE = *
  /FIRST = FirstFlag
  /LAST  = LastFlag
  /BY ClientID.

RANK VARIABLES=MM (A) BY ClientID
  /RANK INTO TrCount2
  /PRINT=NO
  /TIES=CONDENSE.

FORMATS MM (F2.0) TrCount2 (F1.0).

Andy W wrote

First and last can be identified via MATCH FILES, e.g.

SORT CASES BY ClientID TrMonth.
MATCH FILES FILE = *
  /FIRST = FirstFlag
  /LAST = LastFlag
  /BY ClientID.

And FirstFlag and LastFlag will be dummy variables. From there you can create your own combined variable.
...

[David Marso]

Good Catch!
Here is a nifty (IMNSHO) approach to the RECODE:

COMPUTE NumMo=(INDEX('JanFebMarAprMayJunJulAugSepOctNovDec',Mo,3)+2)/3.

--

PRogman wrote

Sorting on TrMonth may give an alphabetic order...
I would suggest a recode  before matching, and then RANK :

RECODE TrMonth ('Jan'=1) ('Feb'=2) ('Mar'=3) ('Apr'= 4) ('May'= 5) ('Jun'= 6)
               ('Jul'=7) ('Aug'=8) ('Sep'=9) ('Oct'=10) ('Nov'=11) ('Dec'=12) INTO MM.

SORT CASES BY ClientID MM.

MATCH FILES
  FILE = *
  /FIRST = FirstFlag
  /LAST  = LastFlag
  /BY ClientID.

RANK VARIABLES=MM (A) BY ClientID
  /RANK INTO TrCount2
  /PRINT=NO
  /TIES=CONDENSE.

FORMATS MM (F2.0) TrCount2 (F1.0).

Andy W wrote

First and last can be identified via MATCH FILES, e.g.

SORT CASES BY ClientID TrMonth.
MATCH FILES FILE = *
  /FIRST = FirstFlag
  /LAST = LastFlag
  /BY ClientID.

And FirstFlag and LastFlag will be dummy variables. From there you can create your own combined variable.
...

[Andy W]

Yes thank you - good catch. With this particular format you can also use ALTER TYPE to change it to MONTH3 format and then sort.

***********************************.
DATA LIST FREE / trmon (A3).
BEGIN DATA
jan feb mar apr may jun jul aug
sep oct nov dec
END DATA.

SORT CASES BY trmon.
LIST trmon.
ALTER TYPE trmon (A = MONTH3).
SORT CASES BY trmon (D).
LIST trmon.
***********************************.