Home Posts tagged "text formatting"

Tag: text formatting

SPSS Macros for replacing missing data

SPSS macro

SPSS Macros for replacing missing data

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.

I wrote a few SPSS Macros that make it very easy to replace the missing values on your data.  Here are the two macros

 
*///////////////.
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.

 

SPSS missing values & corresponding “missing” syntax to solve your needs

SPSS missing values

SPSS missing values

The syntax around SPSS variables with missing 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.

Paste plain text- Use 7 lines of AutoHotKey to easily strip complex RTF/HTML formatting

Paste plain text

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.

paste plain text

Automating prep work for SQL in list query of hundreds-thousands of items

SQL in List

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 script replaces 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