Sometimes you need to change your data before you can analyse it. For example, you might have a field called age but you want to look at the percentages in different age groups. You might want 0-19 in one group, 20-29 in another, 30-39 in another, 40-64 in another, and finally 65+ in another.
How do you get from data like this:
To a report table like this:
The easiest way is to use the built-in recoding functionality of SOFA Statistics (see below). This makes it easy, for example, to map ranges of values to single values. If you are wanting to do something more complex, e.g. averaging the values from multiple fields, it is possible to do so using a spreadsheet before importing/reimporting, or SQLite Database Browser. Finally, if the dataset is small, there is the option of manual data entry.
A video is available showing how to recode data: Recoding data video
Sometimes you need to do something involving multiple variables e.g. making a new variable from the average of three other variables. Or you may have some other, more sophisticated data manipulation requirements. The easiest way to do this is in a spreadsheet before importing (or reimporting) the data.
Another option is to manipulate data already inside SOFA. SOFA stores its data in an SQLite database called sofa_db. It will be stored in a folder like “C:\Documents and Settings\username\sofastats\_internal” or “/home/username/sofastats/_internal”. You can alter the data directly using the free and open source program SQLite Database Browser
The following syntax works in SQLite (common field types are INTEGER, TEXT, and NUMERIC):
ALTER TABLE mytable ADD newvar INTEGER
The following syntax shows how flexible this approach is:
UPDATE mytable SET newvar = Total/2
UPDATE mytable SET newvar = (var1 + var2 + var3)/3
You can also use this approach to alter values in an existing variable. You can also restrict the changes using a WHERE clause e.g.
UPDATE mytable SET existingvar = “Invalid data” WHERE var1 > 100 OR var2 > 100
Once you have started using SQL there is very little you cannot do in data manipulation. The SQLite SQL syntax documentation is here: SQL As Understood By SQLite