SPSSX Discussion

Autorecode can't work with Do if .. End if - looking for workaround or some Macro help.

Classic

List

Threaded

5 messages Options

Mark Webb-5

Autorecode can't work with Do if .. End if - looking for workaround or some Macro help.

I attempted to put an AutoRecode inside a Do if ... End if loop. Unsuccessful. Think I need a Macro or some other work around.

What I'm attempting.

Store    Time Auto#1 AutoNeeded
A          16.14    1              1
A          16.14    1              1
A          17.21    3              2
A          18.20    4              3
B          16.14    1              4
B          16.14    1              4
B          16.78    2              5
B          16.78    2              5
etc.
Above data - 2 stores [A,B] with time of transaction [sorted Store/Time]
I want to code transaction that occur at the same time by store - as shown in column AutoNeeded.
I want to enter the data into a Market Basket/Association Analysis algorithm and need "transactions" i.e. I want the column labelled AutoNeeded.
Because there is duplication in the transaction times between stores [not within as my time variable goes to split seconds] the SPSS Autorecode does not work.
You can run Autorecode on filtered cases but then the autorecodes start from 1 after each filter.
I need to Autorecode [or maybe sequentially number transactions] within Store but not start numbering from 1 each time - any suggestions?

Thanking all in advance.
Regards

-- 
Mark Webb

Line +27 (21) 786 4379
Cell +27 (72) 199 1000 [Poor reception]
Fax  +27 (86) 260 1946 

Skype       tomarkwebb 
Email       [hidden email]

===================== 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

Re: Autorecode can't work with Do if .. End if - looking for workaround or some Macro help.

If you can sort the data, you can use LAG to make a counter.

******************************************************.
DATA LIST FREE /Store (A1) Time Auto#1 AutoNeeded (3F4.2).
BEGIN DATA
A 16.14 1 1
A 16.14 1 1
A 17.21 3 2
A 18.20 4 3
B 16.14 1 4
B 16.14 1 4
B 16.78 2 5
B 16.78 2 5
END DATA.

SORT CASES BY Store Time.
DO IF ($casenum = 1).
COMPUTE AutoR = 1.
ELSE IF Store = LAG(Store) AND Time = LAG(Time).
COMPUTE AutoR = LAG(AutoR).
ELSE.
COMPUTE AutoR = LAG(AutoR) + 1.
END IF.
EXECUTE.
******************************************************.

Andy W
apwheele@gmail.com
http://andrewpwheeler.wordpress.com/

Bruce Weaver

Re: Autorecode can't work with Do if .. End if - looking for workaround or some Macro help.

Administrator

Here's another variation on Andy's theme. It uses SUM in a single compute rather than the DO-IF structure.

SORT CASES BY Store Time.
COMPUTE #DiffStore = Store NE LAG(Store).
COMPUTE #DiffTime = Time NE LAG(Time).
******************************************.
COMPUTE AutoR =
SUM(LAG(AutoR), #DiffStore OR #DiffTime).
******************************************.
FORMATS Auto#1 to AutoR(F2.0).
LIST.

Output:

Store Time Auto#1 AutoNeeded AutoR

A 16.14 1 1 1
A 16.14 1 1 1
A 17.21 3 2 2
A 18.20 4 3 3
B 16.14 1 4 4
B 16.14 1 4 4
B 16.78 2 5 5
B 16.78 2 5 5

Number of cases read: 8 Number of cases listed: 8

The expression

#DiffStore OR #DiffTime

has a value of 0 only if both store and time remain the same as on the previous row. Otherwise, it will have a value of 1.

Andy W wrote

If you can sort the data, you can use LAG to make a counter.

******************************************************.
DATA LIST FREE /Store (A1) Time Auto#1 AutoNeeded (3F4.2).
BEGIN DATA
A 16.14 1 1
A 16.14 1 1
A 17.21 3 2
A 18.20 4 3
B 16.14 1 4
B 16.14 1 4
B 16.78 2 5
B 16.78 2 5
END DATA.

SORT CASES BY Store Time.
DO IF ($casenum = 1).
COMPUTE AutoR = 1.
ELSE IF Store = LAG(Store) AND Time = LAG(Time).
COMPUTE AutoR = LAG(AutoR).
ELSE.
COMPUTE AutoR = LAG(AutoR) + 1.
END IF.
EXECUTE.
******************************************************.

--
Bruce Weaver
bweaver@lakeheadu.ca
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

PLEASE NOTE THE FOLLOWING:
1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above.
2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/).

Andy W

Re: Autorecode can't work with Do if .. End if - looking for workaround or some Macro help.

I learn new things all the time. I'm surprised the first two COMPUTE statements resolve to 1 for the first case, since LAG is missing data. For typical variables if you use

COMPUTE A = B NE C.

and either B or C is missing the resulting variable A will be missing as well.

Andy W
apwheele@gmail.com
http://andrewpwheeler.wordpress.com/

Bruce Weaver

Re: Autorecode can't work with Do if .. End if - looking for workaround or some Macro help.

Administrator

Speaking of learning new things all the time, I just discovered that my syntax worked because Store was a string variable. If both Store and Time are numeric, it does not work. Here is an example.

DATA LIST FREE /Store (A1) StoreNum(F1) Time Auto#1 AutoNeeded (3F4.2).
BEGIN DATA
A 1 16.14 1 1
A 1 16.14 1 1
A 1 17.21 3 2
A 1 18.20 4 3
B 2 16.14 1 4
B 2 16.14 1 4
B 2 16.78 2 5
B 2 16.78 2 5
END DATA.

SORT CASES BY Store Time.
COMPUTE #DiffStore = Store NE LAG(Store).
COMPUTE #DiffStoreNum = StoreNum NE LAG(StoreNum).
COMPUTE #DiffTime = Time NE LAG(Time).
******************************************.
COMPUTE AutoR1 =
SUM(LAG(AutoR1), #DiffStore OR #DiffTime).
COMPUTE AutoR2 =
SUM(LAG(AutoR2), #DiffStoreNum OR #DiffTime).
******************************************.
FORMATS Auto#1 AutoNeeded AutoR1 AutoR2(F2.0).
LIST.

OUTPUT:
Store StoreNum Time Auto#1 AutoNeeded AutoR1 AutoR2

A 1 16.14 1 1 1 .
A 1 16.14 1 1 1 0
A 1 17.21 3 2 2 1
A 1 18.20 4 3 3 2
B 2 16.14 1 4 4 3
B 2 16.14 1 4 4 3
B 2 16.78 2 5 5 4
B 2 16.78 2 5 5 4

Number of cases read: 8 Number of cases listed: 8

So the method Andy posted originally is probably the safest approach.

Andy W wrote

I learn new things all the time. I'm surprised the first two COMPUTE statements resolve to 1 for the first case, since LAG is missing data. For typical variables if you use

COMPUTE A = B NE C.

and either B or C is missing the resulting variable A will be missing as well.