Open SAV file and create an XL map of contents

Needed to write some code today to open up an Statistics sav file and create an XL map file of the metadata. The next step in the process will be to write a IBM Statistics syntax script to change the content in the SAV file based on the information in the XL file. Here is the first bit of the code , lets call it stage 1. If you want stage 2 let me know.

So lets declare some vars

CONST sDataPath = "c:\temp"
CONST sDataName = "IBV_A069_EPH.sav"
CONST sXLMap = "SAV_MAP.xls"

Dim oDSCs, oDSC, oMDM, oVar,oElement
Dim oExcel,oSheet

Dim iVarCount,iRow

And now lets go ahead and open up the SAV file using the Statistics DCS

Set oDSCs = CreateObject("MRDSCReg.Components")
Set oDSC = oDSCs["mrSavDsc"]
Set oMDM = oDSC.Metadata.Open(sDataPath + sDataName)

Then lets open up our XL file. I am assuming it is already made and contains a tab called Labels in it. We are also going to give the columns some names

Set oSheet = oExcel.Sheets["Labels"]
iRow = 1
oSheet.cells[1][1].value = "Variable Name"
oSheet.cells[1][2].value = "New Variable Name"
oSheet.cells[1][3].value = "Type"
oSheet.cells[1][4].value = "Value"
oSheet.cells[1][5].value = "Label"

Next we need to loop the variables in the file outputting the data we find to the relevant places in our sheet

	for iVarCount = 0 to oMDM.Variables.count-1
		iRow = iRow + 1
		Set oVar = oMDM.Variables[iVarCount]	
			oSheet.cells[iRow][1].value = oVar.Name
			oSheet.cells[iRow][3].value = "L"
			oSheet.cells[iRow][5].value = TextOnly(oVar.Label)
			oSheet.cells[iRow][6].value = oVar.dataType

			For each oElement in oVar.Elements.Elements
	   			iRow = iRow + 1
	   			oSheet.cells[iRow][3].value = "C"
	   			oSheet.cells[iRow][4].value = oElement.Element.NativeValue
	   			oSheet.cells[iRow][5].value = TextOnly(oElement.Label)	   			

and finally we need out TextOnly Function. This will allow us to strip out HTML codes that may be in our labels

Function TextOnly(sValue)
	Dim sNew,iCount,bStart
	For iCount = 0 to len(sValue) -1
		if ( find( "<",mid(sValue,iCount,1) )>-1 ) then 
			bStart = true
		End if
		if ( bStart = true ) Then
			sNew = sNew + mid(sValue,iCount,1)
		End If
		if ( find( ">",mid(sValue,iCount,1) )>-1 ) then 
			bStart = false
		End if
	TextOnly = sNew
End Function

Me and Confirmit Exports

I have been working for a few weeks now on data exported from Confirmit and i thought i would share some of the things i have to do and how i do them to get the sav file in to a workable format. These tips may just make your life a little easier if you are doing the same thing.

I would be interested in hearing the things you have to do when getting data from Confirmit. It seams a little strange to me that we have to do these things and it may just be our Vendor that needs teaching but if it is the Confirmit export itself then we may be able to help them make it better. Anyway please share your thoughts i am very interested in what others have to say.

%d bloggers like this: