Produce a grid table by aggregating the data

[jagadishpchary]

Hi,

I have a data for 2 variables one is "IN" data and another is "OUT" data (shown in the attached excel sheet named# "Data"). So I would like to produce a grid table as shown in the sheet name "Table_output" - you can find the aggregations in the cells. Would like to do the same with the SPSS syntax. please help me. Switching_data.xlsx

[Andy W]

Here is one possible way to do this. What I do is 1) reshape the data from wide to long, and then 2) make indicator variables for in and out, then 3) aggregate by summing those indicator variables.

*******************************************.
SPSSINC GETURI DATA
URI="http://spssx-discussion.1045642.n5.nabble.com/file/n5729790/Switching_data.xlsx"
FILETYPE=XLSX DATASET=Data
/OPTIONS SHEETNAME="Data" READNAMES=YES ASSUMEDSTRWIDTH=32767.

*1 reshape.
VARSTOCASES /MAKE Brand FROM In Out /INDEX Type.
*2 indicator variables.
COMPUTE In = (Type = 1).
COMPUTE Out = (Type = 2).
*3 aggregate.
DATASET DECLARE TableOutput.
AGGREGATE OUTFILE='TableOutput'
  /BREAK Brand
  /In = SUM(In)
  /Out = SUM(Out).
DATASET ACTIVATE TableOutput.
COMPUTE Net = In - Out.
*******************************************.

[jagadishpchary]

Andy - Thanks a bunch for your reply and the out put is exactly what I've shown in the excel report.
However can we generate a similar table output (frequency table) in the SPSS output editor? We need not create variables but to show the data in a tabular form (with Base count) in a SPSS window.

I think my explanation in the post is not giving the full details on the job to be done. Sorry for that.

Your help is greatly appreciated.

[Andy W]

Once you make the data you can just use SUMMARIZE (or other table commands, like CTABLES) to put the data into a table in the output. E.g.

SUMMARIZE
  /TABLES=Brand In Out Net
  /FORMAT=LIST NOCASENUM TOTAL
  /TITLE='Case Summaries'
  /MISSING=VARIABLE
  /CELLS=SUM.

[jagadishpchary]

So does it mean that we need to create a new dataset (for BRAND, IN, OUT and NET) to create this output or we could even do this by using # variables? Sorry if I'm seeking too much :)...

[jagadishpchary]

Andy - I'm able to produce table as suggest by you above - however unable to get percentage count for In and Out.
Eg. for Brand 16, (22/131)*100=16.8%. Could you please help?

[Andy W]

You can use AGGREGATE to add value to the active dataset. Continuing the earlier example:

AGGREGATE OUTFILE=* MODE=ADDVARIABLES /BREAK /Total = SUM(In).
COMPUTE PerBran = In/Total.

[David Marso]

Please look at the AGGREGATE and COMPUTE commands ;-)
----

jagadishpchary wrote

Andy - I'm able to produce table as suggest by you above - however unable to get percentage count for In and Out.
Eg. for Brand 16, (22/131)*100=16.8%. Could you please help?

[jagadishpchary]

Andy - Great, this is exactly what I wanted...Thanks a LOT for your help.
David - Certainly...I will work on those commands now