Converting a table to Data File

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

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.

Example of VLookup with data in 2 different sheets.

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

Another one,
=VLOOKUP(O2,B2:K41,5)

In this example O2 (the first condition) is the address of the value that you want to MATCH, for example registration number, ssn etc. (the index) with  the source data file. You can also provide a value here, but giving a cell address makes it easier to copy a range. The second term, B2:K41 is the range of source data in which the first column includes your INDEX variable. The third value, 5 is the column number after the index. For example if your INDEX is in column B and your VALUE (ie name) is in column E, you should write 4 here). Make sure to remember that you need to select the complete range of columns for the lookup. For example, if you have school names in column A,  freq of particular symptom in column B and you want to get the total number of health room visits from columns E (while D has the corresponding school name), you need to select both column D and E for the second condition (not just one column), then put 1 for the index number.

Comparing/Updating multiple files against a Master file

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

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

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

Working with a sub-group of data

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

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

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.