Counting caseworkers

[Nora Douglas]

Hello all,

This seems like it should be so simple, but the solution is eluding me.  I want to count how many caseworkers are in each unit within each region.  Each caseworker can have multiple cases (so each row represents a case and the caseworker ID can show up more than once under ID_WORKER) - I don't want to know how many cases they have, I want to know how many workers there are.

For the sample data set below, I want my output to tell me that there are 2 caseworkers in UNIT 1 REGION 1 (ID_WORKER 123 and 456), 2 caseworkers in UNIT 3 REGION 1 (ID_WORKER 789 and 234), four caseworkers in UNIT 1 REGION 2 (ID_WORKER 876, 987, 453, and 756) and 2 caseworkers in UNIT 2 REGION 2 (ID_WORKER 567 and 678).

I am thinking I should aggregate the file somehow, but I'm getting stuck.

Any help would be greatly appreciated.

Nora

REG UNIT ID_WORKER
1     1     123
1     1     123
1     1     123
1     1     456
1     1     456
1     3     789
1     3     789
1     3     234
1     3     234
2     1     876
2     1     987
2     1     453
2     1     756
2     2     567
2     2     567
2     2     678






Nora E. Douglas, PhD, Program Specialist
Adult Protective Services
Texas Department of Family and Protective Services
701 West 51st Street, Suite 500, Austin, Texas 78751
[phone]
[hidden email]
http://www.dfps.state.tx.us/adult_protection/

[Edward Boadi]

Hi Nora,

Try the syntax below:

Get file = " Specify location and name of your Datafile" .

Sort cases by  REG  UNIT ID_WORKER .

COMPUTE caseworkers = 1.
EXECUTE.

If (REG eq lag(REG) and UNIT eq lag(UNIT) and ID_WORKER eq lag(ID_WORKER) )   caseworkers = 1 + caseworkers    .
EXECUTE.

Recode  caseworkers  ( 1=1) (ELSE =sysmis).

EXECUTE.

CTABLES
  /VLABELS VARIABLES=caseworkers UNIT REG DISPLAY=DEFAULT
  /TABLE UNIT [C] > REG [C][COUNT F40.0] BY caseworkers [C]
  /CATEGORIES VARIABLES=caseworkers ORDER=A KEY=VALUE EMPTY=EXCLUDE
  /CATEGORIES VARIABLES=UNIT ORDER=A KEY=VALUE EMPTY=EXCLUDE TOTAL=YES POSITION=AFTER
  /CATEGORIES VARIABLES=REG ORDER=A KEY=VALUE EMPTY=EXCLUDE.



Regrads.
Edward.


-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]]On Behalf Of
Nora Douglas
Sent: Wednesday, April 11, 2007 3:50 PM
To: [hidden email]
Subject: Counting caseworkers


Hello all,

This seems like it should be so simple, but the solution is eluding me.  I want to count how many caseworkers are in each unit within each region.  Each caseworker can have multiple cases (so each row represents a case and the caseworker ID can show up more than once under ID_WORKER) - I don't want to know how many cases they have, I want to know how many workers there are.

For the sample data set below, I want my output to tell me that there are 2 caseworkers in UNIT 1 REGION 1 (ID_WORKER 123 and 456), 2 caseworkers in UNIT 3 REGION 1 (ID_WORKER 789 and 234), four caseworkers in UNIT 1 REGION 2 (ID_WORKER 876, 987, 453, and 756) and 2 caseworkers in UNIT 2 REGION 2 (ID_WORKER 567 and 678).

I am thinking I should aggregate the file somehow, but I'm getting stuck.

Any help would be greatly appreciated.

Nora

REG UNIT ID_WORKER
1     1     123
1     1     123
1     1     123
1     1     456
1     1     456
1     3     789
1     3     789
1     3     234
1     3     234
2     1     876
2     1     987
2     1     453
2     1     756
2     2     567
2     2     567
2     2     678






Nora E. Douglas, PhD, Program Specialist
Adult Protective Services
Texas Department of Family and Protective Services
701 West 51st Street, Suite 500, Austin, Texas 78751
[phone]
[hidden email]
http://www.dfps.state.tx.us/adult_protection/

[meljr]

One trick is to creat a variable to give you something to aggregate like so:

compute counter = 1.
fre var = counter.

AGGREGATE
  /OUTFILE=*
  /BREAK=id_worker
  /counter_sum = SUM(counter).

list vars = id_worker counter_sum.

Good Luck
meljr

Nora Douglas wrote

Hello all,

This seems like it should be so simple, but the solution is eluding me.  I want to count how many caseworkers are in each unit within each region.  Each caseworker can have multiple cases (so each row represents a case and the caseworker ID can show up more than once under ID_WORKER) - I don't want to know how many cases they have, I want to know how many workers there are.

For the sample data set below, I want my output to tell me that there are 2 caseworkers in UNIT 1 REGION 1 (ID_WORKER 123 and 456), 2 caseworkers in UNIT 3 REGION 1 (ID_WORKER 789 and 234), four caseworkers in UNIT 1 REGION 2 (ID_WORKER 876, 987, 453, and 756) and 2 caseworkers in UNIT 2 REGION 2 (ID_WORKER 567 and 678).

I am thinking I should aggregate the file somehow, but I'm getting stuck.

Any help would be greatly appreciated.

Nora

REG UNIT ID_WORKER
1     1     123
1     1     123
1     1     123
1     1     456
1     1     456
1     3     789
1     3     789
1     3     234
1     3     234
2     1     876
2     1     987
2     1     453
2     1     756
2     2     567
2     2     567
2     2     678






Nora E. Douglas, PhD, Program Specialist
Adult Protective Services
Texas Department of Family and Protective Services
701 West 51st Street, Suite 500, Austin, Texas 78751
[phone]
[e-mail]
http://www.dfps.state.tx.us/adult_protection/

[Nora Douglas]

Thank you Edward!  The syntax Edward provided below worked perfectly.  I don't have a Tables License (bummer!) so I just ran a case summary report instead to get the data in a table format.

Thanks again Edward!

Nora

-----Original Message-----

>From: Edward Boadi <[hidden email]>
>Sent: Apr 11, 2007 3:30 PM
>To: Nora Douglas <[hidden email]>, [hidden email]
>Subject: RE: Counting caseworkers
>
>Hi Nora,
>
>Try the syntax below:
>
>Get file = " Specify location and name of your Datafile" .
>
>Sort cases by  REG  UNIT ID_WORKER .
>
>COMPUTE caseworkers = 1.
>EXECUTE.
>
>If (REG eq lag(REG) and UNIT eq lag(UNIT) and ID_WORKER eq lag(ID_WORKER) )   caseworkers = 1 + caseworkers    .
>EXECUTE.
>
>Recode  caseworkers  ( 1=1) (ELSE =sysmis).
>
>EXECUTE.
>
>CTABLES
>  /VLABELS VARIABLES=caseworkers UNIT REG DISPLAY=DEFAULT
>  /TABLE UNIT [C] > REG [C][COUNT F40.0] BY caseworkers [C]
>  /CATEGORIES VARIABLES=caseworkers ORDER=A KEY=VALUE EMPTY=EXCLUDE
>  /CATEGORIES VARIABLES=UNIT ORDER=A KEY=VALUE EMPTY=EXCLUDE TOTAL=YES POSITION=AFTER
>  /CATEGORIES VARIABLES=REG ORDER=A KEY=VALUE EMPTY=EXCLUDE.
>
>
>
>Regrads.
>Edward.
>
>
>-----Original Message-----
>From: SPSSX(r) Discussion [mailto:[hidden email]]On Behalf Of
>Nora Douglas
>Sent: Wednesday, April 11, 2007 3:50 PM
>To: [hidden email]
>Subject: Counting caseworkers
>
>
>Hello all,
>
>This seems like it should be so simple, but the solution is eluding me.  I want to count how many caseworkers are in each unit within each region.  Each caseworker can have multiple cases (so each row represents a case and the caseworker ID can show up more than once under ID_WORKER) - I don't want to know how many cases they have, I want to know how many workers there are.
>
>For the sample data set below, I want my output to tell me that there are 2 caseworkers in UNIT 1 REGION 1 (ID_WORKER 123 and 456), 2 caseworkers in UNIT 3 REGION 1 (ID_WORKER 789 and 234), four caseworkers in UNIT 1 REGION 2 (ID_WORKER 876, 987, 453, and 756) and 2 caseworkers in UNIT 2 REGION 2 (ID_WORKER 567 and 678).
>
>I am thinking I should aggregate the file somehow, but I'm getting stuck.
>
>Any help would be greatly appreciated.
>
>Nora
>
>REG UNIT ID_WORKER
>1     1     123
>1     1     123
>1     1     123
>1     1     456
>1     1     456
>1     3     789
>1     3     789
>1     3     234
>1     3     234
>2     1     876
>2     1     987
>2     1     453
>2     1     756
>2     2     567
>2     2     567
>2     2     678
>
>
>
>
>
>
>Nora E. Douglas, PhD, Program Specialist
>Adult Protective Services
>Texas Department of Family and Protective Services
>701 West 51st Street, Suite 500, Austin, Texas 78751
>[phone]
>[hidden email]
>http://www.dfps.state.tx.us/adult_protection/

Nora E. Douglas, PhD, Program Specialist
Adult Protective Services
Texas Department of Family and Protective Services
701 West 51st Street, Suite 500, Austin, Texas 78751
[phone]
[hidden email]
http://www.dfps.state.tx.us/adult_protection/

[Richard Ristow]

At 03:50 PM 4/11/2007, Nora Douglas wrote:

>I want to count how many caseworkers are in each unit within each
>region.  Each caseworker can have multiple cases (so each row
>represents a case and the caseworker ID can show up more than once
>under ID_WORKER). I don't want to know how many cases they have, I
>want to know how many workers there are.

For this kind of problem, I favor two AGGREGATEs. Edward Boadi's syntax
works, too, though those EXECUTE statements aren't necessary. And if
desired, his CTABLES command may be replaced by

AGGREGATE OUTFILE=*
   /BREAK= REG UNIT
   /Workers 'Number of caseworkers, for region & unit'
     = SUM(caseworkers).
FORMATS Workers (F4).
LIST.

This is SPSS 15 draft output (WRR-not saved separately). I list the
intermediate stage, counting number of cases per worker, for clarity
and sort of bells-and-whistles; that LIST isn't necessary.

|-----------------------------|---------------------------|
|Output Created               |11-APR-2007 16:54:39       |
|-----------------------------|---------------------------|
REG UNIT ID_WORKER

   1    1     123
   1    1     123
   1    1     123
   1    1     456
   1    1     456
   1    3     789
   1    3     789
   1    3     234
   1    3     234
   2    1     876
   2    1     987
   2    1     453
   2    1     756
   2    2     567
   2    2     567
   2    2     678

Number of cases read:  16    Number of cases listed:  16


AGGREGATE OUTFILE=*
   /BREAK=REG UNIT ID_WORKER
   /WkrLoad 'Number of cases for worker' = NU.

.  /**/ LIST /*-*/.

List
|-----------------------------|---------------------------|
|Output Created               |11-APR-2007 16:54:39       |
|-----------------------------|---------------------------|
REG UNIT ID_WORKER WkrLoad

   1    1     123         3
   1    1     456         2
   1    3     234         2
   1    3     789         2
   2    1     453         1
   2    1     756         1
   2    1     876         1
   2    1     987         1
   2    2     567         2
   2    2     678         1

Number of cases read:  10    Number of cases listed:  10


AGGREGATE OUTFILE=*
   /BREAK=REG UNIT
   /Workers 'Number of caseworkers, for region & unit' = NU.
LIST.

List
|-----------------------------|---------------------------|
|Output Created               |11-APR-2007 16:54:40       |
|-----------------------------|---------------------------|
REG UNIT Workers

   1    1       2
   1    3       2
   2    1       4
   2    2       2

Number of cases read:  4    Number of cases listed:  4

===========================================
APPENDIX: Test data (from original posting)
===========================================
DATA LIST LIST SKIP = 1 /
   REG UNIT ID_WORKER.
BEGIN DATA
REG UNIT ID_WORKER
1     1     123
1     1     123
1     1     123
1     1     456
1     1     456
1     3     789
1     3     789
1     3     234
1     3     234
2     1     876
2     1     987
2     1     453
2     1     756
2     2     567
2     2     567
2     2     678
END DATA.
FORMATS REG UNIT  (F3)
        /ID_WORKER (F4).
LIST.

[Beadle, ViAnn]

You're on the right track. Aggregate breaking on unit, region, and id, asking for the validn as the summary stat. Then in summarize, break on unit and region, asking for validn as the summary stat.

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Nora Douglas
Sent: Wednesday, April 11, 2007 2:50 PM
To: [hidden email]
Subject: [BULK] Counting caseworkers
Importance: Low

Hello all,

This seems like it should be so simple, but the solution is eluding me.  I want to count how many caseworkers are in each unit within each region.  Each caseworker can have multiple cases (so each row represents a case and the caseworker ID can show up more than once under ID_WORKER) - I don't want to know how many cases they have, I want to know how many workers there are.

For the sample data set below, I want my output to tell me that there are 2 caseworkers in UNIT 1 REGION 1 (ID_WORKER 123 and 456), 2 caseworkers in UNIT 3 REGION 1 (ID_WORKER 789 and 234), four caseworkers in UNIT 1 REGION 2 (ID_WORKER 876, 987, 453, and 756) and 2 caseworkers in UNIT 2 REGION 2 (ID_WORKER 567 and 678).

I am thinking I should aggregate the file somehow, but I'm getting stuck.

Any help would be greatly appreciated.

Nora

REG UNIT ID_WORKER
1     1     123
1     1     123
1     1     123
1     1     456
1     1     456
1     3     789
1     3     789
1     3     234
1     3     234
2     1     876
2     1     987
2     1     453
2     1     756
2     2     567
2     2     567
2     2     678






Nora E. Douglas, PhD, Program Specialist
Adult Protective Services
Texas Department of Family and Protective Services
701 West 51st Street, Suite 500, Austin, Texas 78751
[phone]
[hidden email]
http://www.dfps.state.tx.us/adult_protection/