SPSS Macro or Looping Function

[JasonPallant]

Hi All,

I am hoping to get some advice about a possible macro or looping expression that may save me some time here.

I have 50 columns of data across 90,000 rows that stipulate product departments that each individual viewed during a specific web visit. Each column could take one of 72 numeric values (e.g. 10, 57, 231 etc.) or be missing because the individual did not view any items.

What I am trying to achieve is to add extra columns that count how many times each department is viewed (i.e. how many times each value occurs across each row).

A simple (but lengthy) way I have found is to use a count function for each department value, e.g. the syntax below counts how many times the value 10 occurs across the 50 rows (labelled department 1 to 50 to stipulate the product department of item 1 to 50):

COUNT CountDept10=department1 department2 department3 department4 department5 department6 department7
department8 department9 department10 department11 department12 department13 department14
department15 department16 department17 department18 department19 department20 department21
department22 department23 department24 department25 department26 department27 department28
department29 department30 department31 department32 department33 department34 department35
department36 department37 department38 department39 department40 department41 department42
department43 department44 department45 department46 department47 department48 department49
department50(10).
EXECUTE.

Rather than having to copy this syntax 72 times and replace the 10 with each unique department value, I am hoping there is a way to give SPSS a list of the values and tell it to repeat the syntax replacing the 10 with each value in turn.

Any thoughts/ advice would be greatly appreciated!
Jason

 

[Injektilo]

I'm not savvy enough to come up with a macro/loop function that will elegantly solve your problem. However, what I would do is build your syntax command in Excel, and then use Excel's auto-fill feature to change the value being counted from 1 to 72. Then you use the concatenate function to tie the commands back together to paste into the SPSS syntax file.

So in one column you'll have "Countdept", and in the 2nd column your first value (1 in this case, but that will increment row to row). Then paste the 50 dept variables in the third column ending with " (", in the 4th column reference the 2nd column's cell (so =B1 in your first row), and in the last column ")."

Copy and paste all the way down to row 72 your 1st, 3rd, 4th, and 5th column. In the 2nd column, start incrementing down the rows with 1, 2, 3, and double click on the lower right hand corner of the cell for Excel to auto-fill the rest to 72.

Somewhere in the right hand side, use in each row the following command =concatenate(A1, B1, etc..) and click on the lower right end of the cell and drag that down to row 72. Then copy and paste that to the SPSS syntax file.

 

[JasonPallant]

Thanks Injektilo, thats a great backup if I can't get a macro to do it