A solution in CTables to delete columns

[Ki Park]

Hi I am trying to find a solution in CTables to delete columns
(FIPS/counties) when using combined with OMS. My goal is to have county
specific CTABLES that contains state (aggregated all counties) total %.

The Ctable syntax for county "1" is:

CTABLES
  /TABLE VAR1 [C][COLPCT.VALIDN '' PCT40.1, TOTALS[UCOUNT '' F40.0]] BY FIPS
[C]
  /SLABELS POSITION=ROW
  /CATEGORIES VARIABLES=VAR1 ORDER=A KEY=VALUE EMPTY=INCLUDE TOTAL=YES
POSITION=AFTER MISSING=EXCLUDE
  /CATEGORIES VARIABLES=FIPS [1, OTHERNM] EMPTY=INCLUDE TOTAL=YES
POSITION=AFTER.

VAR1 is the variable of interest (e.g. education) across more than 100
counties (FIPS) and I am trying DELETE all counties with the exception of
'chosen' county and TOTAL (of all counties aggregated) to export the output
in XLSX.

I have seen a solution that Jon Peck gave using PCOMPUTE within CTables
(https://developer.ibm.com/answers/questions/226222/deleting-not-hiding-rows-in-custom-tables.html),
and although it works, it becomes a very long code due to the number of
counties.

I am wondering if there is a way of doing this within CTABLE that will allow
to delete columns that is not part of chosen county.

Thanks. Ki




--
Sent from: http://spssx-discussion.1045642.n5.nabble.com/

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Ki Park]

Jon Peck kindly replied to this question through IBM SPSS developer site
-where I posted a similar message- and he reached to a solution. The code
is:


*************Edited with county name.
* Encoding: UTF-8.
* Run CTABLES for all FIPS codes in dataset.
begin program.
import spss, spssdata, spssaux
# The command consists of four parts.  The main body is part2.
cmdtemplate = """
/PPROPERTIES &x LABEL = "County %%" FORMAT=COUNT F40.0, COLPCT.COUNT PCT40.1
HIDESOURCECATS=YES
/TABLE q2 [C][COLPCT.VALIDN '' PCT40.1, TOTALS[UCOUNT '' F40.0]] BY FIPS [C]
/TITLES TITLE="%s"
/SLABELS POSITION=ROW
/CATEGORIES VARIABLES=q2 ORDER=A KEY=VALUE EMPTY=INCLUDE TOTAL=YES
POSITION=AFTER MISSING=EXCLUDE
/CATEGORIES VARIABLES=FIPS [&x """

therest = """  ] EMPTY=INCLUDE TOTAL=YES POSITION=AFTER."""

# find all FIPS values
allvalues = set([int(item[0]) for item in spssdata.Spssdata("FIPS",
names=False).fetchall()])
suppressor = "\n+".join(["0*[%s]" % item for item in sorted(allvalues)])
fipslabels = spssaux.VariableDict("FIPS")["FIPS"].ValueLabels

catlist = " ".join(str(s) for s in allvalues)
for val in allvalues:
    # pexpr is mostly the body of the PCOMPUTE
    pexpr = """CTABLES
/PCOMPUTE &x = """ + """EXPR([%s]\n +""" % val  + suppressor + ")"
# put together and run.
    spss.Submit(pexpr + cmdtemplate % fipslabels.get(str(val), " ") +
catlist + therest )
end program.

*************End of the codes.

The dataset has this structure: The counties (FIPS) has value labels for
each of "n" counties, and Q2 is a measure of exercise (or anything) with
values of 1, 2, or 3.

FIPS ,Q2
25, 2      
1,1
57,3
111,2
.
.
.
n, 1

You could also use this (all fake) data to test the code.

FIPS_dta.sav
<http://spssx-discussion.1045642.n5.nabble.com/file/t340420/FIPS_dta.sav>  
Thanks.

Ki




--
Sent from: http://spssx-discussion.1045642.n5.nabble.com/

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Bruce Weaver]

Thanks for letting us know.  This could save someone who is not following the
other forum the trouble of re-inventing the wheel.  ;-)  



Ki Park wrote
> Jon Peck kindly replied to this question through IBM SPSS developer site
> -where I posted a similar message- and he reached to a solution. The code
> is:
>
> --- snip the rest ---





-----
--
Bruce Weaver
[hidden email]
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.

--
Sent from: http://spssx-discussion.1045642.n5.nabble.com/

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD