SPSSX Discussion

More Data Restructure Questions

Classic

List

Threaded

7 messages Options

DKUKEC

Re: More Data Restructure Questions

This is an instance where VARSTOCASES isn't well suited IMO. Here I will show an example using lag given your data with one important difference, you need to have a variable that associates the comment with the charges (e.g. in your example an id that associates "Failure to Appear" with the first two records and an id that associates "to Appear in CourtA" and "before Judge Dread" with the 4th record).

Here I changed your BookingNumber variable to conform to this. I assume this is possible to create yourself or is already within your data (and it just isn't illustrated in your data snippet). Code example below, but in a nutshell I basically reverse sort, and then propogate the comment field downward within the BookingNumber id.


***************************************************************************************************************.
data list free / InmateID (A3) BookingNumber (F6.0) BookingDate (DATE11) ChargeCount (F1.0)
ChargeCode (F4.1) ChargeDescription (A30) ChargeSquence (F7.0).
begin data
001         201312            09-JAN-2013 1                 934.1          "Cocaine Possession"   1223454 
001         201312            09-JAN-2012 1                 934.2          "Cocaine Trafficking"    1223455 
001         201312            09-JAN-2012 0                 *                "Failure to Appear"       1223456 
001         201344            12-JAN-2013 1                 934.1          "Cocaine Possession"    5345677 
001         201344            12-JAN-2012 0                 *                "to Appear in CourtA"    5345678 
001         201344            12-JAN-2012 0                 *                "before Judge Dread"    5345679 
end data.
dataset name charges.

*reverse sort by Charge Sequence.
sort cases by ChargeSquence (D).

*create comment field and fill in if applicable.
string comment (A90).
if ChargeCount = 0 comment = ChargeDescription.
 
*now propogate the field downward until the end - you need a unique ID field for individual records!.
if BookingNumber = lag(BookingNumber) comment = LTRIM(CONCAT(RTRIM(comment)," ",RTRIM(lag(comment)))).

sort cases by ChargeSquence (A).
select if ChargeCount <> 0.
LIST.
***************************************************************************************************************.

This is an unfortunate way for your database to hold information. But hopefully this helps!

Andy W
apwheele@gmail.com
http://andrewpwheeler.wordpress.com/

DKUKEC

Re: More Data Restructure Questions

In reply to this post by DKUKEC

Thank you Andy,

This worked.

Take care,
Damir

David Marso

Re: More Data Restructure Questions

Administrator

In reply to this post by DKUKEC

SAVE the comment rows into a separate file.
Apply CASESTOVARS to that file. Concatenate the relevant fields.
Match this as a TABLE back to the original.
Fini.

DKUKEC wrote

I am working with a table that includes all charges associated with inmates booked into our county jail; therefore, the unit of count is charges. Regretably, rather than use a "comments" variable the case management system uses the ChargeCode and ChargeDescription variables (both are String) to identify comments and make comments.

For example, the records below reflect one inmate, with two different bookings (admissions) into the county jail for drug charges.

InmateID BookingNumber BookingDate ChargeCount ChargeCode ChargeDescription ChargeSquence
001 201312 09-JAN-2013 1 934.1 Cocaine Possession 1223454
001 201312 09-JAN-2012 1 934.2 Cocaine Trafficking 1223455
001 201367 09-JAN-2012 0 * Failure to Appear 1223456
001 201344 12-JAN-2013 1 934.1 Cocaine Possession 5345677
001 201344 12-JAN-2012 0 * to Appear in CourtA 5345678
001 201378 12-JAN-2012 0 * before Judge Dread 5345679

I would like to take the comments from the ChargeDescription var and restructure the data so that they appear with the corresponding booking (admissions) as a Comments variable. For simplicity and space I have excluded BookingNumber ChargeCount and ChargeSequence. I understand that with the restructing some values may be excluded.

New Structure:

InmateID BookingDate ChargeCode ChargeDescription Comment
001 09-JAN-2013 934.1 Cocaine Possession Failure to Appear
001 09-JAN-2012 934.2 Cocaine Trafficking Failure to Appear
001 12-JAN-2013 934.1 Cocaine Possession to Appear in CourtA - before Judge Dread

I've explored and tried CASETOVARS from previous syntax provided by David M and others, however, I have had little luck. Let me know if you have any questions concerning my lengthly post. Any suggested syntax would be greatly appreciated.

Sincerely,
Damir

Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me.
---
"Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis."
Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?"

David Marso

Re: More Data Restructure Questions

Administrator

ie:
(Using Andy's DATA LIST with mods for dinosaur version).
DATA LIST LIST / InmateID (A3) BNumber (F6.0) BDate (DATE11) CCount (F1.0)
CCode (F4.1) CDescr (A30) CSquence (F7.0).
BEGIN DATA
001 201312 09-JAN-2013 1 934.1 "Cocaine Possession" 1223454
001 201312 09-JAN-2012 1 934.2 "Cocaine Trafficking" 1223455
001 201312 09-JAN-2012 0 * "Failure to Appear" 1223456
001 201344 12-JAN-2013 1 934.1 "Cocaine Possession" 5345677
001 201344 12-JAN-2012 0 * "to Appear in CourtA" 5345678
001 201344 12-JAN-2012 0 * "before Judge Dread" 5345679
END DATA.
LIST.

DO IF NOT MISSING(CCode).
XSAVE OUTFILE 'C:\Trans.sav' .
END IF.
EXE.
SELECT IF MISSING(CCode).
MATCH FILES / FILE * / DROP CCount CCode CSquence.
CASESTOVARS /ID = inmateid bnumber .
STRING @ (A1).
STRING COMMENT (A255).
DO REPEAT V=cdescr.1 TO @.
COMPUTE COMMENT = CONCAT(LTRIM(RTRIM(COMMENT))," ",V).
END REPEAT.
COMPUTE COMMENT = LTRIM(COMMENT).
MATCH FILES / FILE 'C:\Trans.sav' /TABLE * / BY inmateid bnumber /DROP =cdescr.1 TO @.
EXE.

David Marso wrote

SAVE the comment rows into a separate file.
Apply CASESTOVARS to that file. Concatenate the relevant fields.
Match this as a TABLE back to the original.
Fini.

DKUKEC wrote

I am working with a table that includes all charges associated with inmates booked into our county jail; therefore, the unit of count is charges. Regretably, rather than use a "comments" variable the case management system uses the ChargeCode and ChargeDescription variables (both are String) to identify comments and make comments.

For example, the records below reflect one inmate, with two different bookings (admissions) into the county jail for drug charges.

InmateID BookingNumber BookingDate ChargeCount ChargeCode ChargeDescription ChargeSquence
001 201312 09-JAN-2013 1 934.1 Cocaine Possession 1223454
001 201312 09-JAN-2012 1 934.2 Cocaine Trafficking 1223455
001 201367 09-JAN-2012 0 * Failure to Appear 1223456
001 201344 12-JAN-2013 1 934.1 Cocaine Possession 5345677
001 201344 12-JAN-2012 0 * to Appear in CourtA 5345678
001 201378 12-JAN-2012 0 * before Judge Dread 5345679

I would like to take the comments from the ChargeDescription var and restructure the data so that they appear with the corresponding booking (admissions) as a Comments variable. For simplicity and space I have excluded BookingNumber ChargeCount and ChargeSequence. I understand that with the restructing some values may be excluded.

New Structure:

InmateID BookingDate ChargeCode ChargeDescription Comment
001 09-JAN-2013 934.1 Cocaine Possession Failure to Appear
001 09-JAN-2012 934.2 Cocaine Trafficking Failure to Appear
001 12-JAN-2013 934.1 Cocaine Possession to Appear in CourtA - before Judge Dread

I've explored and tried CASETOVARS from previous syntax provided by David M and others, however, I have had little luck. Let me know if you have any questions concerning my lengthly post. Any suggested syntax would be greatly appreciated.

Sincerely,
Damir

DKUKEC

RE: More Data Restructure Questions

Thank you David.

Much appreciated.

From: David Marso [via SPSSX Discussion] [mailto:ml-node+[hidden email]]
Sent: Thursday, January 10, 2013 10:39 AM
To: Damir Kukec
Subject: Re: More Data Restructure Questions

David Marso wrote

SAVE the comment rows into a separate file.
Apply CASESTOVARS to that file. Concatenate the relevant fields.
Match this as a TABLE back to the original.
Fini.

DKUKEC wrote

Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me.

If you reply to this email, your message will be added to the discussion below:

http://spssx-discussion.1045642.n5.nabble.com/More-Data-Restructure-Questions-tp5717307p5717317.html

To unsubscribe from More Data Restructure Questions, click here.
NAML

Under Florida law, e-mail addresses are public records. If you do not want your e-mail address released in response to a public records request, do not send electronic mail to this entity. Instead, contact this office by phone or in writing.

Jo Fennessey

Automatic reply: More Data Restructure Questions

I will be away from the office on Friday, January 11 with limited access to email. Will return on Monday, January 14.