SPSS Statistics

SPSS Statistics

Your hub for statistical analysis, data management, and data documentation. Connect, learn, and share with your peers! 

 View Only

  • 1.  using custom currency format for changing the format of a cell

    Posted Tue March 22, 2022 09:01 AM
    Hi, I need to format some cells in a pivot table as, for instance, (15.6) instead of 15.6, so trying to use the custom currency format (CCA, ..., CCE) by defining it using SetPreference method. Below is my code and I can see that SetPreference lines are working, but it is giving the error as "Not a valid argument value" for the last line (SetNumericFormatAt). I have also tried using "(#.###) : −(#.###)" instead of "CCA" in the last line. Can anyone please tell me what is being wrong here?

    begin program.
    import spss, spssaux
    import SpssClient
    SpssClient.StartClient()

    SpssClient.SetPreference(SpssClient.PreferenceOptions.CustomOutputFormat,"CCA")
    SpssClient.SetPreference(SpssClient.PreferenceOptions.AllValuesPrefix,"(")
    SpssClient.SetPreference(SpssClient.PreferenceOptions.AllValuesSuffix,")")
    SpssClient.SetPreference(SpssClient.PreferenceOptions.NegativeValuesPrefix,"-")
    SpssClient.SetPreference(SpssClient.PreferenceOptions.NegativeValuesSuffix,"")
    SpssClient.SetPreference(SpssClient.PreferenceOptions.DecimalSeparator,"period")

    oDoc = SpssClient.GetDesignatedOutputDoc()
    oItems = oDoc.GetOutputItems()
    for index in range(oItems.Size()):
    oItem = oItems.GetItemAt(index)
    if oItem.GetType() == SpssClient.OutputItemType.PIVOT:
    pTable = oItem.GetSpecificType()
    datacells = pTable.DataCellArray()
    RowLabels = pTable.RowLabelArray()
    for i in range(datacells.GetNumRows()):
    if float(datacells.GetValueAt(i,2)) >= 25 and float(datacells.GetValueAt(i,2)) < 500:
    datacells.SetNumericFormatAt(i,0,"CCA")
    end program.

    Much apprecieted,
    Zula

    ------------------------------
    Munkhzul Zookhuu
    ------------------------------

    #SPSSStatistics


  • 2.  RE: using custom currency format for changing the format of a cell

    Posted Tue March 22, 2022 12:09 PM
    Hi. I tried scientfic notation and it worked.

    dataset close all.
    output close all.
    data list free /x.
    begin data.
    1 2 3
    end data.
    fre var all.

    begin program Python3.
    import spss, spssaux
    import SpssClient
    SpssClient.StartClient()

    SpssClient.SetPreference(SpssClient.PreferenceOptions.CustomOutputFormat,"CCA")
    SpssClient.SetPreference(SpssClient.PreferenceOptions.AllValuesPrefix,"(")
    SpssClient.SetPreference(SpssClient.PreferenceOptions.AllValuesSuffix,")")
    SpssClient.SetPreference(SpssClient.PreferenceOptions.NegativeValuesPrefix,"-")
    SpssClient.SetPreference(SpssClient.PreferenceOptions.NegativeValuesSuffix,"")
    SpssClient.SetPreference(SpssClient.PreferenceOptions.DecimalSeparator,"period")

    oDoc = SpssClient.GetDesignatedOutputDoc()
    oItems = oDoc.GetOutputItems()
    for index in range(oItems.Size()):
      oItem = oItems.GetItemAt(index)
      if oItem.GetType() == SpssClient.OutputItemType.PIVOT:
        pTable = oItem.GetSpecificType()
        datacells = pTable.DataCellArray()
        RowLabels = pTable.RowLabelArray()  
        for i in range(datacells.GetNumRows()):
          x=float(datacells.GetValueAt(i,0))
         #datacells.SetNumericFormatAt(i,0,"CCA")
         # THAT FAILS
          datacells.SetNumericFormatAt(i,0,"#.##E+##")
         # THIS WORKS
             
    end program.

    But the user's guide says in Appendix D: "You can create up to five custom currency display formats that can include special prefix and suffix characters and special treatment for negative values. The five custom currency format names are CCA, CCB, CCC, CCD, and CCE. The string can be used to specify the currency formats."

    so according to that, it looks like "CCA" should work. I tried "cca", "Cca", etc. but no luck. Looks like a problem. I'll have to submit it to the Dev team and see if we can get it fixed.



    Original Message


  • 3.  RE: using custom currency format for changing the format of a cell

    Posted Tue March 22, 2022 12:45 PM
    Hi Rick,

    Thanks a lot for looking into it. Yes, with any other standard formats it works and as you pointed out, looking at the documentations, it looks like the custom format should also work. It would be great if you could submit it to the Dev team then and hope it can be fixed. If it is fixed, it is going to be a lot helpful to get rid of something that we do manually in our work. Just to also mention that if I use the Pivot table editor and do the formatting using the menu (Format -> Cell properties -> Format value -> select the custom format), it also works, but the point of this to be able to do it (putting parenthesis around a number in a cell) using the syntax. 

    Will be looking forward to hearing good news soon,
    Zula

    ------------------------------
    Munkhzul Zookhuu
    ------------------------------

    Original Message


  • 4.  RE: using custom currency format for changing the format of a cell

    Posted Tue March 22, 2022 12:59 PM
    Yes, the CCA's see, to be broken currently in the Python preference options.  They used to work.  You can set them, but you can't get them or specify in the NumericFormats api.

    But, the good news is that you don't need to use Python here.
    First, you can set the CCA definitions with the SET command
    SET CCA=...

    Then, after you run the table, you can use the OUTPUT MODIFY command to apply the format.  Here is an example that you can adapt.

    OUTPUT MODIFY NAME=Document1
      /SELECT TABLES
      /IF COMMANDS=["CTables(1)"] LABELS=[EXACT("Table 1")] INSTANCES=[1]
      /TABLECELLS SELECT=[MEAN] SELECTDIMENSION=COLUMNS SELECTCONDITION="25.0<x<500.0" FORMAT="CCA"
        APPLYTO=CELL.



    --



    Original Message


  • 5.  RE: using custom currency format for changing the format of a cell

    Posted Tue March 22, 2022 03:57 PM
    Hi Jon,

    Thanks for your suggestion. I am looking into the OUTPUT MODIFY command as I haven't used it much before. Seeing from the documentation, it seems that, in the /Tablecells Select=, I would need to indicate the labels of the columns that I am going to format, right? Is there no way to indicate the column numbers instead of the labels? Also, more importantly, I have to format some cells of a column based on the values of another column, not the selected column itself, as well as one table could have several columns as base for conditioning for other columns. Can this be feasible with the OUTPUT MODIFY? This was possible with the Python and I have some other things that are also being done by Python, so it was going to be preferable if the CCA worked for Python. If it used to work before, do you think there is a way to alert the Python people for having it fixed? 

    To elaborate more on what exactly I am doing, I have prepared the following example table before and after formatting. Basically, if the unweighted denominator1 is between 25-49, the percentages that were calculated based on this denominator (indicator1.1 and 1.2) to be formatted with (), if the denominator is less than 25 then the percentage to be replaced by (*) and if 0, to be replaced by -. The latter 2 are working just fine. Similar to be done for the Indicator2.1 based on values of the Unweighted denominator2. As there are lots of tables with relatively long and unique labels in the columns and users/countries with different level of experience, I would like to have a code that is as generic as possible. 

    I will keep looking into the OUTPUT MODIFY, but just in case if I am missing something for what it can do, please let me know. Also, it would be much appreciated if perhaps some other solution/idea comes up to your mind after seeing what exactly I am trying to do. 

    and again, what could I do for having the CCA be fixed for Python, particularly if it used to work?

    With thanks,
    Zula

    ------------------------------
    Munkhzul Zookhuu
    ------------------------------

    Original Message


  • 6.  RE: using custom currency format for changing the format of a cell

    Posted Tue March 22, 2022 07:40 PM
    I'll look into this further tomorrow, but it would help to have an spv file of the sort you want to modify..
    As for column selection, you can use the POSITION keyword in OUTPUT MODIFY  to pick the columns you want to work on, but the command restricts the selection to be based on the same cell.

    There is an extension command SPSSINC MODIFY TABLES that is more powerful than OUTPUT MODIFY, which was designed to be an easier to use subset of MODIFY TABLES.  The key thing here is that it allows you to plug in small Python functions to extend its capabilities.  Being in Python, it would still not be able to use the custom currency settings directly, but it could change the cell values where needed to strings and handle the formatting in code.  This would be pretty easy unless there are a lot of different formatting issues..  It could reference different columns (or rows) to determine the selection.  Of course, using your current code, you could just reconstruct the cell values formatted the way you want them without using CCA etc.  I haven't tried MODIFY TABLES with custom currencies, but I am assuming that it would run into the same bug.

    The customstylefunctions.py file, which is installed with the extension, contains a lot of plugin functions, so looking at that would help you understand what it can do.

    There is a bug filed on this now, I think, but I can't speak to when it might get fixed.

    --



    Original Message


  • 7.  RE: using custom currency format for changing the format of a cell

    Posted Wed March 23, 2022 11:03 AM
      |   view attached
    Thanks Jon, for your insights. I have tried to attach a spv (SPSS 26) file here, but apparently it is not supported. So, can I send to your email?

    ------------------------------
    Munkhzul Zookhuu
    ------------------------------

    Attachment(s)

    xlsx
    Formatted table_weighted and suppressed.xlsx   19 KB 1 version
    Original Message


Related Content