Skip to main content

Data & Intelligence

IBM SPSS Statistics Syntax – Restructuring Your Files

Continuing my discussion on the use of SPSS Syntax scripting to perform file operations, today I’ve investigated how to use SPSS to perform some very general physical file restructuring. Why is this important? It’s important because it again shows how SPSS can be used to arrange the data pool into the most effective format based upon the analysis procedure that you want to perform on it.

First, as a reminder, the SPSS Restructure Data Wizard can be used to restructure your data. Using your provided selections, the wizard replaces a file with a new, restructured file.

The wizard can:

• Restructure selected variables into cases

• Restructure selected cases into variables

• Transpose all data

But, what if you don’t want to “drive the UI”? And what if you want to automate the restructuring process to be used again and again? Of course, the answer is a Syntax script.

Cases to Variables Example

To illustrate the point, we’ll use a “cases to variables” example and leverage a file from my previous post.

In that post, I utilized a syntax script to generate a (merged) file of quarterly hours-billed data. In that file, I had a record (or case) for each consultant, for each month:

fileagain

In this case, I’d rather have my file have a single record (case) per consultant. Of course I could use other methods and tools to restructure my file, but SPSS gives me everything I need. I can write a short Syntax script to do exactly that.

SORT CASES and CASESTOVARS

In my example script, I used 2 SPSS functions SORT CASES and CASETOVARS. CASETOVARS is the “heavy lifter” in that I supply a variable name to be used as the “unique ID” (consultant”) and SPSS does the rest.

In addition, in the new file it doesn’t make sense to “repeat” the month variable on the same record (as it can be easily inferred from hoursbilled.1, hoursbilled.2 and hoursbilled.3), so I’ve added the /DROP parameter to the script so that field (variable) gets dropped out of my new file.

Data Intelligence - The Future of Big Data
The Future of Big Data

With some guidance, you can craft a data platform that is right for your organization’s needs and gets the most return from your data capital.

Get the Guide

A side note, when you are writing your scripts it is important to maintain GOOD programming style, so get familiar with 2 icons I circled here – comment line and auto-indent – both will help you write and maintain easily readable and understandable scripts.

syntaxwindow

After we save and run the script, we get the file in the format I am looking for:

restructuredfile

And of course, SPSS Statistics viewer gives the following evidence:

viewerwindow

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

My next post will show the results of some of my explorations into the extensive SPSS Syntax function library!

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Jim Miller

Mr. Miller is an IBM certified and accomplished Senior Project Leader and Application/System Architect-Developer with over 30 years of extensive applications and system design and development experience. His current role is National FPM Practice Leader. His experience includes BI, Web architecture & design, systems analysis, GUI design and testing, Database modeling and systems analysis, design, and development of Client/Server, Web and Mainframe applications and systems utilizing: Applix TM1 (including TM1 rules, TI, TM1Web and Planning Manager), dynaSight - ArcPlan, ASP, DHTML, XML, IIS, MS Visual Basic and VBA, Visual Studio, PERL, Websuite, MS SQL Server, ORACLE, SYBASE SQL Server, etc. His Responsibilities have included all aspects of Windows and SQL solution development and design including: analysis; GUI (and Web site) design; data modeling; table, screen/form and script development; SQL (and remote stored procedures and triggers) development and testing; test preparation and management and training of programming staff. Other experience includes development of ETL infrastructure such as data transfer automation between mainframe (DB2, Lawson, Great Plains, etc.) systems and client/server SQL server and Web based applications and integration of enterprise applications and data sources. In addition, Mr. Miller has acted as Internet Applications Development Manager responsible for the design, development, QA and delivery of multiple Web Sites including online trading applications, warehouse process control and scheduling systems and administrative and control applications. Mr. Miller also was responsible for the design, development and administration of a Web based financial reporting system for a 450 million dollar organization, reporting directly to the CFO and his executive team. Mr. Miller has also been responsible for managing and directing multiple resources in various management roles including project and team leader, lead developer and applications development director. Specialties Include: Cognos/TM1 Design and Development, Cognos Planning, IBM SPSS and Modeler, OLAP, Visual Basic, SQL Server, Forecasting and Planning; International Application Development, Business Intelligence, Project Development. IBM Certified Developer - Cognos TM1 (perfect score 100% on exam) IBM Certified Business Analyst - Cognos TM1

More from this Author

Follow Us