copy/ duplicate/ triplicate records via syntax

[Mark Webb-5]

I have item level sales data with this format -
id,item,value,n_items
1,Abc,33.33,3
2,Cde,10.10,1

Record 1 represents 3 instances of item Abc and their total value. Item value is 11.11.
Records 2 represents 1 instance of item Cde with a value of 10.10

Item 1 is aggregates the 3 items Abc and sums their individual values.

I would like to get un-aggregated data by [somehow] creating 2 copies of record 1, add it to file, and divide the value by 3.
Output would look like-

1,Abc,11.11,1
2,Cde,10.10,1
1,Abc,11.11,1
1,Abc,11.11,1

Any useful suggestions? The duplication of records part is where I get stuck. The solution must be syntax based as there are many records.
Thanks in advance.
===================== 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]

XSAVE works well for this task.

*******************************.
DATA LIST LIST (",") / id (F1.0) item (A3) value (F4.2) n_items (F1.0).
BEGIN DATA
1,Abc,33.33,3
2,Cde,10.10,1
END DATA.
DATASET NAME Test.

*Using XSAVE.
FILE HANDLE save /NAME = "C:\Users\andrew.wheeler\Desktop".
LOOP #i = 1 TO n_items.
  XSAVE OUTFILE = "save\Test.sav" /DROP n_items.
END LOOP.

GET FILE = "save\Test.sav".
DATASET NAME TestExpand.
*******************************.

I've also used a trick with VARSTOCASES to do this as well (if you really do not want to save to an external file). Basically you make a set of dummy variables for how ever many n_items there are, leaving the rest of the vector blank, which are then dropped on the VARSTOCASES command. You need to know the maximum number of possible replications though with this technique.

*******************************.
*Alternative using VARSTOCASES.
DATASET ACTIVATE Test.
VECTOR MaxN(3).
LOOP #i = 1 TO n_items.
  COMPUTE MaxN(#i) = 1.
END LOOP.
VARSTOCASES /MAKE MaxN FROM MaxN1 TO MaxN3.
MATCH FILES FILE = * /DROP n_items MaxN.
*******************************.

I'm sure other solutions could be reached as well, but these are two I have used in the past.

[Bruce Weaver]

I was thinking XSAVE for this too.  But note that in the original file, VALUE = the total value for the 3 items.  But the OP wants the value of one item in the final data set.  So before the XSAVE, you'll need:

COMPUTE value = value / n_items.

HTH.

Andy W wrote

XSAVE works well for this task.

*******************************.
DATA LIST LIST (",") / id (F1.0) item (A3) value (F4.2) n_items (F1.0).
BEGIN DATA
1,Abc,33.33,3
2,Cde,10.10,1
END DATA.
DATASET NAME Test.

*Using XSAVE.
FILE HANDLE save /NAME = "C:\Users\andrew.wheeler\Desktop".
LOOP #i = 1 TO n_items.
  XSAVE OUTFILE = "save\Test.sav" /DROP n_items.
END LOOP.

GET FILE = "save\Test.sav".
DATASET NAME TestExpand.
*******************************.

I've also used a trick with VARSTOCASES to do this as well (if you really do not want to save to an external file). Basically you make a set of dummy variables for how ever many n_items there are, leaving the rest of the vector blank, which are then dropped on the VARSTOCASES command. You need to know the maximum number of possible replications though with this technique.

*******************************.
*Alternative using VARSTOCASES.
DATASET ACTIVATE Test.
VECTOR MaxN(3).
LOOP #i = 1 TO n_items.
  COMPUTE MaxN(#i) = 1.
END LOOP.
VARSTOCASES /MAKE MaxN FROM MaxN1 TO MaxN3.
MATCH FILES FILE = * /DROP n_items MaxN.
*******************************.

I'm sure other solutions could be reached as well, but these are two I have used in the past.

[Mark Webb-5]

Thanks Andy and Bruce
Working perfectly.
Regards
On 2014/07/20 02:10 PM, Andy W wrote:

XSAVE works well for this task.

*******************************.
DATA LIST LIST (",") / id (F1.0) item (A3) value (F4.2) n_items (F1.0).
BEGIN DATA
1,Abc,33.33,3
2,Cde,10.10,1
END DATA.
DATASET NAME Test.

*Using XSAVE.
FILE HANDLE save /NAME = "C:\Users\andrew.wheeler\Desktop".
LOOP #i = 1 TO n_items.
  XSAVE OUTFILE = "save\Test.sav" /DROP n_items.
END LOOP.

GET FILE = "save\Test.sav".
DATASET NAME TestExpand.
*******************************.

I've also used a trick with VARSTOCASES to do this as well (if you really do
not want to save to an external file). Basically you make a set of dummy
variables for how ever many n_items there are, leaving the rest of the
vector blank, which are then dropped on the VARSTOCASES command. You need to
know the maximum number of possible replications though with this technique.

*******************************.
*Alternative using VARSTOCASES.
DATASET ACTIVATE Test.
VECTOR MaxN(3).
LOOP #i = 1 TO n_items.
  COMPUTE MaxN(#i) = 1.
END LOOP.
VARSTOCASES /MAKE MaxN FROM MaxN1 TO MaxN3.
MATCH FILES FILE = * /DROP n_items MaxN.
*******************************.

I'm sure other solutions could be reached as well, but these are two I have
used in the past.



-----
Andy W
[hidden email]
http://andrewpwheeler.wordpress.com/
--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/copy-duplicate-triplicate-records-via-syntax-tp5726767p5726768.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

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

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