A lot of online vendor tools provide a data value on a multi-select question however they leave the value missing if the respondent didn’t select it. Most of the analysis I want to do needs to have a zero (or some other value) present in order to calculate the statistics correctly.
The syntax around SPSS variables withmissing values is not intuitive, confusing, and poorly documented!
I know of 3 different types of commands and knowing which one to use when is not clear. Setting SPSS missing values is a great way to simplify your analysis. It is also a user-friendly way to remove (hide) outliers. This video gives a short demo of how to use the three that I use frequently.
If you want to declare a value in a cell as missing the following syntax will give you a good start.
if Var1=1 Var1=$sysmis.
exe.
If you want to remove the values that are in a variable (define them as missing) the following syntax will be what you need.
MISSING VALUES Var1 to Var10 (99).
SPSS missing values Macros
Below are two macros to help with missing data. The first one is used when you first want to see if there is a given value present in another variable before declaring it recoding the missing a zero. The second one will recode all variables with missing values a zero.
*///////////////.
DEFINE !Rep_Miss (Beg !TOKENS (1) /Prez !TOKENS (1) /End !TOKENS (1))
Do if !PREZ>0.
do repeat v=!BEG to !END.
if missing (v) v=0.
end repeat.
end if.
exe.
!ENDDEFINE.
*///////////////.
!Rep_Miss Prez=presentvariable Beg=v11 End=v19.
*///////////////.
DEFINE !Rep_Miss2 (Beg !TOKENS (1) /End !TOKENS (1))
do repeat v=!BEG to !END.
if missing (v) v=0.
end repeat.
exe.
!ENDDEFINE.
*///////////////.
/*!Rep_Miss2 Beg=v11 End=v19.
I (like everyone else in the world) frequently copy-paste between various programs. While some programs like Word and Excel allow for paste plain text, a lot do not.
Paste plain text
I wrote a small script in AutoHotKey which takes the contents of the clipboard and strips it of HTML/RTF format. This way it allows me to paste as plain text in any program by hitting a key combination (Control+G). What’s great is that I don’t actually lose the original format so, if I decide I actually wanted the original content, I can still just paste with Control+V and I’m good to go!
Below is the actual code to paste plain text in AutoHotKey.
Often I have a list of hundreds (sometimes thousands) of items that I need to insert into a query. Unfortunately I can only add 99 items at a time.
This video demonstrates how my scriptreplaces the first 98 line breaks with commas and wrap items already having a comma with special characters to escape them. This is perfect for my SQL in list query! What would have taken me several minutes or longer is now done in the blink of an eye (while also escaping illegal characters). The below video demonstrates it’s usage and further down the page is the AutoHotkey code that cranks it out!
Video demonstrating how the SQL in list works
AutoHotkey code:
;***********save clipboard to retore later*******************
Store:=ClipboardAll ;Store full version of Clipboard
clipboard = ; Empty the clipboard
SendInput, ^c ;changd from Send 11/23
ClipWait, 1
If ErrorLevel ;Added errorLevel checking
{
MsgBox, No text was sent to clipboard
Return
}
;***********remove blank lines*******************
Loop,parse,Clipboard,`n`r ;loops through lines
{
New:=RegExReplace(A_LoopField,"^\s?$","") ;if line only has white space
If (new!="")
NewText.=new "`r`n"
}
Clipboard:=NewText
NewText:=""
;***********clean up the text*******************
Trimmed:=RegExReplace(clipboard, "m)^\s?(.*)\s?$","$1") ;trim spaces from both sides
;***********************wrap special characthers********************************.
StringReplace, Trimmed, Trimmed, &, '||'&'||',All ;wrap commas
StringReplace, Trimmed, Trimmed, ', '',All ; double up ' so it escapes it
;***********************Create groups of 999********************************.
Loop,
{
NewStr := RegExReplace(Trimmed, "`r`n", "','",1,998,1) ;998 is max
AddBreak := RegExReplace(NewStr, "`r`n", "`n`r*****************" . (A_Index*1000)+1 . "*****************`r",1,1,1)
If (AddBreak=NewStr) ;check to see if done looping through all items
break
Trimmed:=AddBreak ;
}
Clipboard := RegExReplace(NewStr, "','$", "") ; Delete ending ','
;***********restore clipboard*******************
SendEvent , ^v
Sleep, 50
Clipboard:=Store
;***********wipe out vars of SQL in list*******************
New:="" , newstr:="" , newText:="" , Trimmed:="" , Addbreak:="" ; Empty vars
return