Recode Makro - Statistica Visual Basic - Statistica - Dell Community

Recode Makro

Recode Makro

This question is not answered

Hi Everyone,

I had a few problems today preparing a makro to do a

recode on one of my variables.

 

Sub Main

' Makro aufgezeichnet 04.05.2010 17:15:59

    Dim AO As AnalysisOutput

    Dim AWB As Workbook

    Dim S1 As Spreadsheet

    Set S1 = ActiveDataSet

    S1.Recode 197, scRecodeLeaveOtherValue, "", _

        Array(scSelectionConditionIncludeIf, scSelectionConditionIncludeIf,

                  scSelectionConditionIncludeIf), _

        Array("v8 = 50825", "v8 = 47638", "v8 = 51105"), _

        Array("Köln", "Straelen", "Köln")

End Sub


The problem is that there are up to 15.000 different numbers that could

 appear in v8.

When I copy this from excel to this makro he cuts off the most of the numbers.

Thanks for your ideas

olli

All Replies
  • Hello Olli,

    Which version of STATISTICA you are using? Can the input

    spreadsheet be attached to the post?

    Thanks.

     

  • Olli

    If I understand what you are trying to do, you could try using the in()

    function rather than listing all of the zip codes in separate conditions. 

    This would significantly reduce the number of conditions you have to

    specify, which might help.

    S1.Recode 197, scRecodeLeaveOtherValue, "", _
            Array(scSelectionConditionIncludeIf, scSelectionConditionIncludeIf), _
            Array("In(v8,50416,50441,50448,50467,50532,50535,50597,50657,
                            50667,50668,50670,50672,50674,50676,50677,50678,50679,50732,      50733,50735,50737,50739,50765,50767,50769,50823,50825,50827,    50829,50835,50858,50859,50885,50923,50931,50933,50935,50937,

     50939,50968,50969,50996,50997,50999,51057,51061,51063,51065,

     51067,51069,51075,51103,51105,51107,51109,51114,51129,51143,

     51145,51147,51149,51153,51170,51175,53115)", _

                  "In(v8,47638)"), _
            Array("
    Köln","Straelen")

    If keeping track of all of the Array() elements in the Recode function become

    too difficult and performance is not an issue, you could simply specify a

    sequence of separate functions like this:

    S1.Recode 197, scRecodeLeaveOtherValue, "", _
            scSelectionConditionIncludeIf, _
                "In(v8,50416,50441,50448,50467,50532,50535,50597,50657,50667,

     50668,50670,50672,50674,50676,50677,50678,50679,50732,50733,

     50735,50737,50739,50765,50767,50769,50823,50825,50827,50829,

     50835,50858,50859,50885,50923,50931,50933,50935,50937,50939,

     50968,50969,50996,50997,50999,51057,51061,51063,51065,51067,

     51069,51075,51103,51105,51107,51109,51114,51129,51143,51145,

     51147,51149,51153,51170,51175,53115)", _
                "Köln"

    S1.Recode 197, scRecodeLeaveOtherValue, "", _
            scSelectionConditionIncludeIf, _
                "In(v8,47638)", _
                "Straelen"
     

    Hope this helps

    Matt

     

  • First of all, thanks for the quick answers. Sadly the in() function is not that usefull in my case. The Problem is the large number of zip codes and related citys.

    The following link contains a .pdf of the codes and cities i like to put into the recode makro postet earlier in this treat:

    http://dl.dropbox.com/u/1503717/Spreadsheet_PLZ.pdf

    I hope someone could help so I don't have to fill in all the codes manually.

    Thx

    Olli

  • Olli,

    It seems to me that the simple look-up procedure needs to created that would match the city and zip. Are the data you posted in .pdf available in the form of STATISTICA spreadsheet?

    Thanks.

  • Hi Roman,

    the spreadsheet I posted is an excel sheet which I use as basis. It's just a list of all german postal codes an teh related cities.

    this is my statistica spreadsheet (extract): I'm using Statistica Version 9

    http://dl.dropbox.com/u/1503717/Spreadsheet_Survey.sta

    the problem is, that several numbers belong to one city, so I get an error if I just write it down into the textwerte editor (sorry, but I don't know the englisch meaning for that).

    For the analysis I have to do list of the cities so it's necessary to combine the codes to one city e.g. (

    44623
    44625
    44627
    44628
    44629
    44649
    44651
    44652
    44653

    to the city herne.

    thx for your help

    Olli

  • Olli,

    Your English is perfect, I am not an English expert too. Below is the schema what suppose to happen, this is not a working code, it needs to be adjust to your datasets. Just open new macro and paste the from the below, then edit it accordingly. If you have any problem, let me know and we can dig into the deatils.

    -R.

    -----------------------

    Const VARNUM_ZIPCODES   As Long = 8
    Const VARNUM_ZIPCODES_LOOKUP As Long = 1 'change this to your oZipCodes.ZIPCODE variable number
    Const VARNUM_CITY_LOOKUP As Long = 2 'change this to your oZipCodes.CITY variable number

    Sub Main

     Dim oDataSet As Spreadsheet  'your Spreadsheet_Survey.sta
     Dim oZipCodes As Spreadsheet 'your Excel spreadsheet

     Dim iZipCode As Integer
     Dim sFoundCity As String

     Set oDataSet = ActiveDataSet
     'open oZipCodes spreadsheet here

        For i = 1 To oDataSet.NumberOfCases  'your active input dataset loop
         iZipCode = oDataSet.Value(i, VARNUM_ZIPCODES)
         For j = 1 To oZipCodes.NumberOfCases
       If iZipCode = oDataSet.Value(j, VARNUM_ZIPCODES_LOOKUP) Then
         sFoundCity = oDataSet.Text(j, VARNUM_CITY_LOOKUP) 'zip code is match to the city here
         oDataSet.Value(i, VARNUM_ZIPCODES) = sFoundCity  'zip code is overwritten with city name here
                       'or you can assign a city name other variable by changing the VARNUM_ZIPCODES
        Exit For
       End If
         Next j
        Next i

     oDataSet.Save
     oZipCodes.Close
     Set oZipCodes = Nothing

    End Sub
    ---------------------

  • Hi RomanS,

     

    finally I found time to try your macro. Sadly I recognized, that it's not working. I think I did something wrong, 'cause this message pops up:

    "vba-error in macro: *macro1 in row: 10'

    expecting a valid datatype (e.g. integer)"

    ---------------------------------------------------------------------------------------------------------------------------------------------

     '#Language "WWB-COM"
    Option Base 1


    Const VARNUM_ZIPCODES   As Long = 8
    Const VARNUM_ZIPCODES_LOOKUP As Long = 8  'change this to your oZipCodes.ZIPCODE variable number
    Const VARNUM_CITY_LOOKUP As Long = 197   'change this to your oZipCodes.CITY variable number
    Sub Main

     Dim oDataSet As N:\02 PROJEKTE\...Fragebogen100504.sta  'your Spreadsheet_Survey.sta
     Dim oZipCodes As C:\Users\...Liste_PLZ_Staedte.xlsx 'your Excel spreadsheet

     Dim iZipCode As Integer
     Dim sFoundCity As String

     Set oDataSet = ActiveDataSet
     'open oZipCodes spreadsheet here

        For i = 1 To oDataSet.NumberOfCases  'your active input dataset loop
         iZipCode = oDataSet.Value(i, VARNUM_ZIPCODES)
         For j = 1 To oZipCodes.NumberOfCases
       If iZipCode = oDataSet.Value(j, VARNUM_ZIPCODES_LOOKUP) Then
         sFoundCity = oDataSet.Text(j, VARNUM_CITY_LOOKUP) 'zip code is match to the city here
         oDataSet.Value(i, VARNUM_ZIPCODES) = sFoundCity  'zip code is overwritten with city name here
                       'or you can assign a city name other variable by changing the VARNUM_ZIPCODES
        Exit For
       End If
         Next j
        Next i

     oDataSet.Save
     oZipCodes.Close
     Set oZipCodes = Nothing

    End Sub

    -----------------------------------------------------------------------------------------------------------------------------------------------------

     

    maybe someone knows how to fix this?

     

    thx

    olli

  • Hello Olli,

    Would you please zip your macro (and if possible your input data) and send to 

    I will take a look. Thanks,

    DeveloperDocumentation@statsoft.com

  • HI Everyone ,

    Thanks for help me .