Life with SPSS and other similar things

Entering quantitative data into the computer

2012-01-26T00:29:00.000-08:00

Well there are many many ways you can enter the quantitative data (generated from surveys) into a computer. Most of the people I know use either use a simple Excel table or very very complex MS Access data entry file. However both of these programs are not created for data entry purposes and hence are not so handy for that purpose.

There are several software out there in the market which you can buy to do the job, or you can outsource the data for entry or create a program for the very purpose yourself. All of these options require extra resources. SPSS, the most popular data analysis program also has a data entry module which is great but that is not free.

The best option would be to have a data entry program which is made just for that purpose, is easy to learn and use and most importantly is free. Thankfully there are 2 such software:

1) CSPro
"The Census and Survey Processing System (CSPro) is a public domain software package used by hundreds of organizations and tens of thousands of individuals for entering, editing, tabulating, and disseminating census and survey data."

2) EpiData
"EpiData Entry is used for simple or programmed data entry and data documentation. Entry handles simple forms or related systems Optimised documentation."

In my view using using Excel and Access have their strength but for any survey with more than a page long questionnaire and more than 40-50 respondents, it is better to use one or the other of the above.

Range checks, value lengths, logical jumps, automatically calculated fields, data validation on double entry, find duplicates, assign values for missing or not applicable values etc. are some the things that needs to be done to maintain the integrity of data. Both of the above software can do that.

The files that are created in these programs can easily be exported to major analytical packages like SPSS and STATA.

Converting a table to Data File

2012-01-12T20:28:00.000-08:00

I have been looking for this for so long.....

Thank you Mr. John Walkenbach

Here is a nifty Macro in Excel to do that!!!

Sub ReversePivotTable()
'   Before running this, make sure you have a summary table with column headers.
'   The output table will have three columns.    
Dim SummaryTable As Range, OutputRange As Range    
Dim OutRow As Long    
Dim r As Long, c As Long     
On Error Resume Next    
Set SummaryTable = ActiveCell.CurrentRegion    
If SummaryTable.Count = 1 Or SummaryTable.Rows.Count < 3 Then        
MsgBox "Select a cell within the summary table.", vbCritical        
Exit Sub    
End If    
SummaryTable.Select    
Set OutputRange = Application.InputBox(prompt:="Select a cell for the 3-column output", Type:=8) '  
Convert the range     OutRow = 2     Application.ScreenUpdating = False    
OutputRange.Range("A1:C3") = Array("Column1", "Column2", "Column3")    
For r = 2 To SummaryTable.Rows.Count        
For c = 2 To SummaryTable.Columns.Count            
OutputRange.Cells(OutRow, 1) = SummaryTable.Cells(r, 1)            
OutputRange.Cells(OutRow, 2) = SummaryTable.Cells(1, c)            
OutputRange.Cells(OutRow, 3) = SummaryTable.Cells(r, c)            
OutputRange.Cells(OutRow, 3).NumberFormat = SummaryTable.Cells(r, c).NumberFormat            
OutRow = OutRow + 1       
Next c  
 Next r
End Sub

VlookUp in Excel

2012-01-12T13:43:00.000-08:00

Learnt a lot about VlookUp today. It is really one of the most powerful features of MS Excel.

Few things to remember.
The formula will return a "0" (without quotes) if the index variable was matched but there as not corresponding value to return. If the index variable does not match, Excel will return #NA.

-remember the column number you want to retrieve. This column number is counted from the column where you "looked" for the index variable.

-You can lookup in another file.
-Linked files get updated even if they are not open.
-To keep track of what changes has been made it is a good idea to use the "TRACK CHANGES" feature.
-Excel cares about trailing spaces but does not care for difference in CASE and the type of the cell.
-Cells cannot be protected, hence you have to be very careful when you use vlookup.

Will add notes later.

=VLOOKUP(A:A,Sheet3!A:B,2,FALSE)

Comparing/Updating multiple files against a Master file

2012-01-12T13:41:00.000-08:00

UPDATE FILE command can be used for that. More text to add later.

Meanwhile check this page.
http://www.ats.ucla.edu/stat/spss/faq/update.htm

Excel oddities

2012-01-10T13:13:00.000-08:00

Check this page for some oddities and quirks related to Excel.

http://spreadsheetpage.com/index.php/oddities

Working with a sub-group of data

2012-01-05T13:49:00.000-08:00

Sometimes you want to include only a sub-set or sample of the data in your analysis. There are different ways of doing that.

1. Using a sample of the data
If you just want to randomly choose a percentage of the data to work on it, here is the formula to select 30% of the responses:

TEMPORARY. SAMPLE .30.

You can also select a number of cases, ie:

SAMPLE 50 from 100.

will choose 50 cases randomly from the first 100 cases.

Note: Adding the "TEMPORARY" before makes sure that your original data does not change.

2. Selected group of cases

You can use select if (by itself or with temporary) on the basis of a criteria to run your statistics. For example, if you want to see the sleep time for mothers with under 2 year old children, you may have to select Female in the sex field (if there is one) and the age of child. This command can also be used with SYSMIS (system missing values) also.

3. Filter

You can filter the data according to a criteria assigned in a variable ie work only with boys or girls sample or children who are in school or not in school. The only thing to remember is that the variable has to be a dummy variable (with 0 and 1 values). The filter "turns off" the zeros, ie if the variable gender has assigned 0 to boys and 1 to girls, when you use filter it will generate statistics only for girls. To do so,

FILTER BY variable name.

When you are done, don't forget to use:

FILTER OFF.

4. Split file

First of all sort the cases by the variable you want to split the file with.

SORT CASES BY variable name.

Now use the split command.

SPLIT FILE BY variable name.

Don't forget to do turn the split function off.

SPLIT FILE OFF.

(thanks to UCLA SPSS Learning Modules for their online guide)

Uses of (System) missing

2011-11-03T13:20:00.000-07:00

Sometimes you want to assign your newly computed variable a system missing value (which is a . in spss database). Here is the command for that:

COMPUTE temp = $sysmis.

(this syntax will create a variable called temp which will initially have all values set as missing)

For a conditional function:

IF sysmis(v1) v2=$sysmis.

You can also use missing values in RECODE.

RECODE (sysmis=99)

or

RECODE (99=sysmis)

RECODE [your command] (ELSE=sysmis).

Also read the UCLA SPSS page and CDC page on handling of missing data.

PURPOSIVE SAMPLING

2011-11-01T13:03:00.000-07:00

PURPOSIVE SAMPLING - Subjects are selected because of some characteristic. Patton (1990) has proposed the following cases of purposive sampling. Purposive sampling is popular in qualitative research.

Extreme or Deviant Case - Learning from highly unusual manifestations of the phenomenon of interest, such as outstanding success/notable failures, top of the class/dropouts, exotic events, crises.
Intensity - Information-rich cases that manifest the phenomenon intensely, but not extremely, such as good students/poor students, above average/below average.
Maximum Variation - Purposefully picking a wide range of variation on dimensions of interest...documents unique or diverse variations that have emerged in adapting to different conditions. Identifies important common patterns that cut across variations.
Homogeneous - Focuses, reduces variation, simplifies analysis, facilitates group interviewing.
Typical Case - Illustrates or highlights what is typical, normal, average.
Stratified Purposeful - Illustrates characteristics of particular subgroups of interest; facilitates comparisons.
Critical Case - Permits logical generalization and maximum application of information to other cases because if it's true of this once case it's likely to be true of all other cases.
Snowball or Chain - Identifies cases of interest from people who know people who know people who know what cases are information-rich, that is, good examples for study, good interview subjects.
Criterion - Picking all cases that meet some criterion, such as all children abused in a treatment facility. Quality assurance.
Theory-Based or Operational Construct - Finding manifestations of a theoretical construct of interest so as to elaborate and examine the construct.
Confirming or Disconfirming - Elaborating and deepening initial analysis, seeking exceptions, testing variation.
Opportunistic - Following new leads during fieldwork, taking advantage of the unexpected, flexibility.
Random Purposeful - (still small sample size) Adds credibility to sample when potential purposeful sample is larger than one can handle. Reduces judgment within a purposeful category. (Not for generalizations or representativeness.)
Politically Important Cases - Attracts attention to the study (or avoids attracting undesired attention by purposefully eliminating from the sample politically sensitive cases).
Convenience - Saves time, money, and effort. Poorest rational; lowest credibility. Yields information-poor cases.
Combination or Mixed Purposeful - Triangulation, flexibility, meets multiple interests and needs. (Patton, 1990)

Patton, M. Q. (1990). Qualitative evaluation and research methods (2nd ed.). Newbury Park, CA: Sage Publications.

What can you do with COMPUTE in SPSS

2011-10-27T15:00:00.001-07:00

Note what you can do with Mean.# and Sum.# to tackle missing values. And also take note of LAG which takes up the value of the preceding case.

COMPUTE y=ABS(x). absolute value of x. ABS(!7) =7.
COMPUTE y=SQRT(x). square root
COMPUTE y=LN(x). natural logarithm
COMPUTE y=LG10(x). base 10 logarithm
COMPUTE y=EXP(x). exponential: ex
COMPUTE y=TRUNC(x). integer part. TRUNC(5.7)=5.
COMPUTE y=RND(x). round to nearest integer. RND(5.7)=6
COMPUTE y=MOD(x,11). remainder after division by 11
COMPUTE y=SUM(x1,x2,x3). sum of 3 variables if at least one is non-missing
COMPUTE y=SUM.5(x1 TO x10). sum of 10 variables if at least 5 are non-missing.
COMPUTE y=MEAN.2(x1,x2,x3). mean of 3 variables if at least 2 are non-missing
COMPUTE y=LAG(x). x from previous case
COMPUTE y=$SYSMIS. sets Y to sysmis.

Source: SPSS for Windows 8, 9 and 10 by Svend Juul

Dropping missing values in SPSS

2011-10-27T14:50:00.000-07:00

(This needs some more thought and cleaning up)

The SELECT command with the SYSMIS() function can drop all missing cases from the current SPSS data set. Consider the following:

SELECT IF NOT (SYSMIS (amount)). SAVE OUTFILE= 'newfile.sav'.

This example drops all cases whose value of the variable amount is missing, and then saves this data to an SPSS system file called newfile.sav.

If the dataset has more than one coding for missing values, as is often the case for survey data, select all of the different codings for missing values with the AND operator:

SELECT IF NOT (SYSMIS(amount1)) AND NOT (SYSMIS(amount2)). SAVE OUTFILE= 'newfile.sav'.

http://kb.iu.edu/data/afay.html

look at this post (multi variate anal)

2011-10-27T14:48:00.001-07:00

http://core.ecu.edu/psyc/wuenschk/spss/SPSS-MV.htm

little issue with Excel sorting

2011-07-07T13:43:00.000-07:00

I was going nuts trying to sort a little table in Excel which was created using some formulas... Excel was just sorting based on the formula in the cell rather than the cell value.... after googling a lot, I found out that in Excel 2007 the "Calculation" default option is set to "Automatic".... To avoid my problem, I just needed to turn if OFF and switch it to Manual. The option is in "Formulas" tab in Excel 2007, the right most choice "Calculation Options."

Found the answer on a Excel discussion board.

Remove ALL spaces from cells in MS Excel

2011-06-14T14:11:00.000-07:00

one more thing you may have to do when you download your data from surveyshare.com is to remove all unnecessary spaces from certain fields. I my case I had to do it from the email field which is the INDEX key and cannot have any extra character!!! Here is the macro I have found:

Sub TrimEText()
' This module will trim extra spaces from BOTH SIDES and excessive spaces from inside the text.
Dim MyCell As Range
On Error Resume Next
For Each MyCell In Selection.Cells
MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), " ", " ")
MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), " ", " ")
MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), " ", " ")
MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), " ", "")
Next
On Error GoTo 0
End Sub

Really grateful to the author of the macro!!

Excel Macro to convert the CASE of a range of TEXT

2011-06-09T14:41:00.000-07:00

Really useful macro. I used it to clean up surveyshare data file before bringing it to SPSS.

Before using:
"Uncomment" (remove the apostrophe from) the line of code that changes the text to the case you want. For example I needed everything to converted to lower case and hence I removed the apostrophe from "' Rng.Value = StrConv(Rng.Text, vbLowerCase)"

Sub ChangeCase()
Dim Rng As Range
On Error Resume Next
Err.Clear
Application.EnableEvents = False
For Each Rng In Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues).Cells
If Err.Number = 0 Then
' Rng.Value = StrConv(Rng.Text, vbUpperCase)
' Rng.Value = StrConv(Rng.Text, vbLowerCase)
' Rng.Value = StrConv(Rng.Text, vbProperCase)
End If
Next Rng
Application.EnableEvents = True
End Sub

Source: http://www.cpearson.com/excel/ChangingCase.aspx

What is the difference between causation and correlation?

2011-04-27T09:27:00.001-07:00

What is the difference between causation and correlation?

One of the most common errors we find in the press is the confusion between correlation and causation in scientific and health-related studies. In theory, these are easy to distinguish — an action or occurrence can cause another (such as smoking causes lung cancer), or it can correlate with another (such as smoking is correlated with alcoholism). If one action causes another, then they are most certainly correlated. But just because two things occur together does not mean that one caused the other, even if it seems to make sense.

World's baby No. 7 billion could be born in UP - Times Of India

2011-03-07T00:19:00.000-08:00

World's baby No. 7 billion could be born in UP - Times Of India

dos commands site

2011-02-24T13:58:00.000-08:00

http://labnol.blogspot.com/2006/04/useful-windows-xp-dos-commands-tricks.html

Truncating a string variable & other things

2011-02-15T15:02:00.001-08:00

This text has been copied from UCLA website!!!

Create a String Variable up that will be the name converted into upper case, lo that will be the name converted to lower case, and sub that will be the third through eighth character in the persons name. Note that we first had to use the string command to tell SPSS that up lo and sub are string variables that will have a length of up to 14 characters. Had we omitted the string command, these would have been treated as numeric variables, and when SPSS tried to assign a character value to the numeric variables, it would have generated an error. We also create len that is the length of the name variable, and len2

that is the length of the persons name.

STRING up lo (A14)
/sub (A6).

COMPUTE up  = UPCASE(name).
COMPUTE lo  = LOWER(name).
COMPUTE sub  = SUBSTR(name,3,8).
COMPUTE len = LENGTH(name).

COMPUTE len2 = LENGTH(RTRIM(name)).

For more info visit: http://www.ats.ucla.edu/stat/spss/modules/functions.htm

Random Forest

2011-02-08T11:59:00.000-08:00

group of many decision trees. learn more:

http://en.wikipedia.org/wiki/Random_forest

Assigning Student Grades Using Excel

2011-02-08T11:51:00.000-08:00

Here is the formula from MS office website:

=IF(A2>89,"A",IF(A2>79,"B", IF(A2>69,"C",IF(A2>59,"D","F"))))

If more than 6 conditions to check, better to use LOOKUP then IF/THEN

=LOOKUP(A2,{0,60,63,67,70,73,77,80,83,87,90,93,97},{"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"})

source: http://office.microsoft.com/en-us/excel-help/if-HP005209118.aspx

a very simple table using CTables

2011-01-13T18:08:00.000-08:00

	group Universe vs sample
	1 Universe		2 Sample		Total Respondents
	Column N %	Count	Column N %	Count	Column N %	Count
1 Female	53.0%	904	61.0%	153	54.0%	1057
2 Male	46.3%	789	39.0%	98	45.3%	887
Not specified	.7%	12	.0%	0	.6%	12

To get the above table use the following syntex:

CTABLES /TABLE gender2 by group [colpct count]
/CATEGORIES VARIABLES=group TOTAL=YES LABEL='Total Respondents'.

&

	Main groups
	Our big univ		our sample		Total
	Column N %	Count	Column N %	Count	Column N %	Count
1 Female	53.0%	904	61.0%	153	54.0%	1057
2 Male	46.3%	789	39.0%	98	45.3%	887
3 Not specified	.7%	12	.0%	0	.6%	12
Total	100.0%	1705	100.0%	251	100.0%	1956

For the above, here is the syntax (notice the columns also have totals now):
CTABLES /TABLE gender2 by group [colpct count]
/CATEGORIES VARIABLES=group TOTAL=YES LABEL='Total Respondents'
/CATEGORIES VARIABLES= gender2 TOTAL=YES POSITION=AFTER.

Confirmatory vs. Exploratory analysis

2010-12-16T23:19:00.000-08:00

Here is a very nice and succinct analysis of the difference between confirmatory vs. exploratory data analysis:

http://www.geog.ucsb.edu/~joel/g210_w07/lecture_notes/lect01/oh07_01_2.html

So, my dissertation would be more on the exploratory side.....

Date & Time in SPSS

2010-12-14T22:41:00.000-08:00

COMPUTE NewVar = YRMODA(yrVar, moVar, daVar).

to get months out of days data, divide it by 30.4375

365.25 days: the average length of a year in the Julian calendar.

Another good table made with CTable

2010-12-09T15:45:00.000-08:00

Here is the syntax for the above table:
CTABLES
/TABLE cohort1>( Q11a +Q11b +A11c +Q11d +Q11e +Q11f +Q11g +Q11h +Q11i +Q11j) [COUNT ROWPCT]
/SLABELS VISIBLE=NO
/CLABELS ROWLABELS=OPPOSITE
/CATEGORIES VARIABLES= cohort1 TOTAL=YES LABEL='Total'
/CATEGORIES VARIABLES= Q11a Q11b A11c Q11d Q11e Q11f Q11g Q11h Q11i Q11j TOTAL=YES LABEL='RowTotal'
/TITLE TITLE = 'Q11_: Ranking of abilities by students: (row percents) '+
'by cohort'
CAPTION= 'Report created at )TIME on )DATE' ')TABLE'.

Really nice table

2010-12-09T12:24:00.001-08:00

For the above, use this syntax:

CTABLES
/TABLE (Q11a +Q11b +A11c +Q11d +Q11e +Q11f +Q11g +Q11h +Q11i +Q11j) [COLPCT] [COUNT] BY cohort1
/SLABELS VISIBLE=NO
/CATEGORIES VARIABLES=cohort1 TOTAL=YES LABEL='Total Respondents'
/CATEGORIES VARIABLES= Q11a Q11b A11c Q11d Q11e Q11f Q11g Q11h Q11i Q11j [1, 2, 3, 4, SUBTOTAL, OTHERNM] EMPTY=INCLUDE POSITION=AFTER.
/TITLE TITLE = 'Ranking of abilities by students '+
'by cohort'
CAPTION= 'Report created at )TIME on )DATE' ')TABLE'.