Collapsing multiple records by unique ID
Locked
Collapsing multiple records by unique ID
 
|
I now have a file where each ID may have multiple records, and I need to create a file with one record for each ID. Below I have pasted a screenshot with a sample of the data.
 Any help with this would be greatly appreciated! I have attempted to use the aggregate function unsuccessfully as several of the variables are string variables. RJ Bangurah Research Scientist |
Re: Collapsing multiple records by unique ID
Administrator
|
What do you want the file to look like when it has one row per ID? Take GRADE, for example. At the moment, there are multiple values for each ID. Do you want to preserve those raw values (e.g., in multiple columns)? Or do you want one mean grade for each ID? Please provide more details.
--
Bruce Weaver bweaver@lakeheadu.ca http://sites.google.com/a/lakeheadu.ca/bweaver/ "When all else fails, RTFM." PLEASE NOTE THE FOLLOWING: 1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. 2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/). |
RE: Collapsing multiple records by unique ID - Number of numbers in MIME From exceeds maximum threshold - Number of numbers in MIME From exceeds maximum threshold
|
|
Sorry. For each ID, I need to preserve the multiple values
for a variable in separate columns. So using grade as an
example, I would need fields for grade 1, grade2, …gradex for each ID.
The highest number of entries for an ID is 33. I have tried running the
syntax pasted below as well as converting cases to variables, but did not get what
I have described. SORT CASES BY ID. EXECUTE. COMPUTE tasknum=1. DO IF ($CASENUM NE 1). IF (id EQ LAG(ID)) tasknum = LAG(tasknum)+1. END IF. EXECUTE. DO REPEAT trial=EffectiveDate to YR_TRANS_Revised. VECTOR trial(33). COMPUTE trial(tasknum)=trial. END REPEAT. AGGREGATE OUTFILE=* /BREAK=ID /EffectiveDate1= MAX(EffectiveDate1) /EffectiveDate2= MAX(EffectiveDate2) /EffectiveDate3= MAX(EffectiveDate3) /EffectiveDate4= MAX(EffectiveDate4) /EffectiveDate5= MAX(EffectiveDate5) //EffectiveDate6= MAX(EffectiveDate6) /EffectiveDate7= MAX(EffectiveDate7) /EffectiveDate8= MAX(EffectiveDate8) /EffectiveDate9= MAX(EffectiveDate9) /EffectiveDate10= MAX(EffectiveDate10) /EffectiveDate11= MAX(EffectiveDate11) /EffectiveDate12= MAX(EffectiveDate12) /EffectiveDate13= MAX(EffectiveDate13) /EffectiveDate14= MAX(EffectiveDate14) /EffectiveDate15= MAX(EffectiveDate15) /EffectiveDate16= MAX(EffectiveDate16) /EffectiveDate17= MAX(EffectiveDate17) /EffectiveDate18= MAX(EffectiveDate18) /EffectiveDate19= MAX(EffectiveDate19) /EffectiveDate20= MAX(EffectiveDate20) /EffectiveDate21= MAX(EffectiveDate21) /EffectiveDate22= MAX(EffectiveDate22) /EffectiveDate23= MAX(EffectiveDate23) /EffectiveDate24= MAX(EffectiveDate24) /EffectiveDate25= MAX(EffectiveDate25) /EffectiveDate26= MAX(EffectiveDate26) /EffectiveDate27= MAX(EffectiveDate27) /EffectiveDate28= MAX(EffectiveDate28) /EffectiveDate29= MAX(EffectiveDate29) /EffectiveDate30= MAX(EffectiveDate30) /EffectiveDate31= MAX(EffectiveDate31) /EffectiveDate32= MAX(EffectiveDate32) /EffectiveDate33= MAX(EffectiveDate33) /EnterCode1= MAX(EnterCode1) /EnterCode2= MAX(EnterCode2) /EnterCode3= MAX(EnterCode3) /EnterCode4= MAX(EnterCode4) /EnterCode5= MAX(EnterCode5) /EnterCode6= MAX(EnterCode6) /EnterCode7= MAX(EnterCode7) /EnterCode8= MAX(EnterCode8) /EnterCode9= MAX(EnterCode9) /EnterCode10= MAX(EnterCode10) /EnterCode11= MAX(EnterCode11) /EnterCode12= MAX(EnterCode12) /EnterCode13= MAX(EnterCode13) /EnterCode14= MAX(EnterCode14) /EnterCode15= MAX(EnterCode15) /EnterCode16= MAX(EnterCode16) /EnterCode17= MAX(EnterCode17) /EnterCode18= MAX(EnterCode18) /EnterCode19= MAX(EnterCode19) /EnterCode20= MAX(EnterCode20) /EnterCode21= MAX(EnterCode21) /EnterCode22= MAX(EnterCode22) /EnterCode23= MAX(EnterCode23) /EnterCode24= MAX(EnterCode24) /EnterCode25= MAX(EnterCode25) /EnterCode26= MAX(EnterCode26) /EnterCode27= MAX(EnterCode27) /EnterCode28= MAX(EnterCode28) /EnterCode29= MAX(EnterCode29) /EnterCode30= MAX(EnterCode30) /EnterCode31= MAX(EnterCode31) /EnterCode32= MAX(EnterCode32) /EnterCode33= MAX(EnterCode33) /LeaveCode1= MAX(LeaveCode1) /LeaveCode2= MAX(LeaveCode2) /LeaveCode3= MAX(LeaveCode3) /LeaveCode4= MAX(LeaveCode4) /LeaveCode5= MAX(LeaveCode5) /LeaveCode6= MAX(LeaveCode6) /LeaveCode7= MAX(LeaveCode7) /LeaveCode8= MAX(LeaveCode8) /LeaveCode9= MAX(LeaveCode9) /LeaveCode10= MAX(LeaveCode10) /LeaveCode11= MAX(LeaveCode11) /LeaveCode12= MAX(LeaveCode12) /LeaveCode13= MAX(LeaveCode13) /LeaveCode14= MAX(LeaveCode14) /LeaveCode15= MAX(LeaveCode15) /LeaveCode16= MAX(LeaveCode16) /LeaveCode17= MAX(LeaveCode17) /LeaveCode18= MAX(LeaveCode18) /LeaveCode19= MAX(LeaveCode19) /LeaveCode20= MAX(LeaveCode20) /LeaveCode21= MAX(LeaveCode21) /LeaveCode22= MAX(LeaveCode22) /LeaveCode23= MAX(LeaveCode23) /LeaveCode24= MAX(LeaveCode24) /LeaveCode25= MAX(LeaveCode25) /LeaveCode26= MAX(LeaveCode26) /LeaveCode27= MAX(LeaveCode27) /LeaveCode28= MAX(LeaveCode28) /LeaveCode29= MAX(LeaveCode29) /LeaveCode30= MAX(LeaveCode30) /LeaveCode31= MAX(LeaveCode31) /LeaveCode32= MAX(LeaveCode32) /LeaveCode33= MAX(LeaveCode33) /Grade1= MAX(Grade1) /Grade2= MAX(Grade2) /Grade3= MAX(Grade3) /Grade4= MAX(Grade4) /Grade5= MAX(Grade5) /Grade6= MAX(Grade6) /Grade7= MAX(Grade7) /Grade8= MAX(Grade8) /Grade9= MAX(Grade9) /Grade10= MAX(Grade10) /Grade11= MAX(Grade11) /Grade12= MAX(Grade12) /Grade13= MAX(Grade13) /Grade14= MAX(Grade14) /Grade15= MAX(Grade15) /Grade16= MAX(Grade16) /Grade17= MAX(Grad17) /Grade18= MAX(Grade18) /Grade19= MAX(Grade19) /Grade20= MAX(Grade20) /Grade21= MAX(Grade21) /Grade22= MAX(Grade22) /Grade23= MAX(Grade23) /Grade24= MAX(Grade24) /Grade25= MAX(Grade25) /Grade26= MAX(Grade26) /Grade27= MAX(Grade27) /Grade28= MAX(Grade28) /Grade29= MAX(Grade29) /Grade30= MAX(Grade30) /Grade31= MAX(Grade31) /Grade32= MAX(Grade32) /Grade33= MAX(Grade33) /SchoolofAttendance1= MAX(SchoolofAttendance1) /SchoolofAttendance2= MAX(SchoolofAttendance2) /SchoolofAttendance3= MAX(SchoolofAttendance3) /SchoolofAttendance4= MAX(SchoolofAttendance4) /SchoolofAttendance5= MAX(SchoolofAttendance5) /SchoolofAttendance6= MAX(SchoolofAttendance6) /SchoolofAttendance7= MAX(SchoolofAttendance7) /SchoolofAttendance8= MAX(SchoolofAttendance8) /SchoolofAttendance9= MAX(SchoolofAttendance9) /SchoolofAttendance10= MAX(SchoolofAttendance10) /SchoolofAttendance11= MAX(SchoolofAttendance11) /SchoolofAttendance12= MAX(SchoolofAttendance12) /SchoolofAttendance13= MAX(SchoolofAttendance13) /SchoolofAttendance14= MAX(SchoolofAttendance14) /SchoolofAttendance15= MAX(SchoolofAttendance15) /SchoolofAttendance16= MAX(SchoolofAttendance16) /SchoolofAttendance17= MAX(SchoolofAttendance17) /SchoolofAttendance18= MAX(SchoolofAttendance18) /SchoolofAttendance19= MAX(SchoolofAttendance19) /SchoolofAttendance20= MAX(SchoolofAttendance20) /SchoolofAttendance21= MAX(SchoolofAttendance21) /SchoolofAttendance22= MAX(SchoolofAttendance22) /SchoolofAttendance23= MAX(SchoolofAttendance23) /SchoolofAttendance24= MAX(SchoolofAttendance24) /SchoolofAttendance25= MAX(SchoolofAttendance25) /SchoolofAttendance26= MAX(SchoolofAttendance26) /SchoolofAttendance27= MAX(SchoolofAttendance27) /SchoolofAttendance28= MAX(SchoolofAttendance28) /SchoolofAttendance29= MAX(SchoolofAttendance29) /SchoolofAttendance30= MAX(SchoolofAttendance30) /SchoolofAttendance31= MAX(SchoolofAttendance31) /SchoolofAttendance32= MAX(SchoolofAttendance32) /SchoolofAttendance33= MAX(SchoolofAttendance33) /YearofTransaction1= MAX(YearofTransaction1) /YearofTransaction2= MAX(YearofTransaction2) /YearofTransaction3= MAX(YearofTransaction3) /YearofTransaction4= MAX(YearofTransaction4) /YearofTransaction5= MAX(YearofTransaction5) /YearofTransaction6= MAX(YearofTransaction6) /YearofTransaction7= MAX(YearofTransaction7) /YearofTransaction8= MAX(YearofTransaction8) /YearofTransaction9= MAX(YearofTransaction9) /YearofTransaction10= MAX(YearofTransaction10) /YearofTransaction11= MAX(YearofTransaction11) /YearofTransaction12= MAX(YearofTransaction12) /YearofTransaction13= MAX(YearofTransaction13) /YearofTransaction14= MAX(YearofTransaction14) /YearofTransaction15= MAX(YearofTransaction15) /YearofTransaction16= MAX(YearofTransaction16) /YearofTransaction17= MAX(YearofTransaction17) /YearofTransaction18= MAX(YearofTransaction18) /YearofTransaction19= MAX(YearofTransaction19) /YearofTransaction20= MAX(YearofTransaction20) /YearofTransaction21= MAX(YearofTransaction21) /YearofTransaction22= MAX(YearofTransaction22) /YearofTransaction23= MAX(YearofTransaction23) /YearofTransaction24= MAX(YearofTransaction24) /YearofTransaction25= MAX(YearofTransaction25) /YearofTransaction26= MAX(YearofTransaction26) /YearofTransaction27= MAX(YearofTransaction27) /YearofTransaction28= MAX(YearofTransaction28) /YearofTransaction29= MAX(YearofTransaction29) /YearofTransaction30= MAX(YearofTransaction30) /YearofTransaction31= MAX(YearofTransaction31) /YearofTransaction32= MAX(YearofTransaction32) /YearofTransaction33= MAX(YearofTransaction33) /YR_TRANS_Revised1= MAX(YR_TRANS_Revised1) /YR_TRANS_Revised2= MAX(YR_TRANS_Revised2) /YR_TRANS_Revised3= MAX(YR_TRANS_Revised3) /YR_TRANS_Revised4= MAX(YR_TRANS_Revised4) /YR_TRANS_Revised5= MAX(YR_TRANS_Revised5) /YR_TRANS_Revised6= MAX(YR_TRANS_Revised6) /YR_TRANS_Revised7= MAX(YR_TRANS_Revised7) /YR_TRANS_Revised8= MAX(YR_TRANS_Revised8) /YR_TRANS_Revised9= MAX(YR_TRANS_Revised9) /YR_TRANS_Revised10= MAX(YR_TRANS_Revised10) /YR_TRANS_Revised11= MAX(YR_TRANS_Revised11) /YR_TRANS_Revised12= MAX(YR_TRANS_Revised12) /YR_TRANS_Revised13= MAX(YR_TRANS_Revised13) /YR_TRANS_Revised14= MAX(YR_TRANS_Revised14) /YR_TRANS_Revised15= MAX(YR_TRANS_Revised15) /YR_TRANS_Revised16= MAX(YR_TRANS_Revised16) /YR_TRANS_Revised17= MAX(YR_TRANS_Revised17) /YR_TRANS_Revised18= MAX(YR_TRANS_Revised18) /YR_TRANS_Revised19= MAX(YR_TRANS_Revised19) /YR_TRANS_Revised20= MAX(YR_TRANS_Revised20) /YR_TRANS_Revised21= MAX(YR_TRANS_Revised21) /YR_TRANS_Revised22= MAX(YR_TRANS_Revised22) /YR_TRANS_Revised23= MAX(YR_TRANS_Revised23) /YR_TRANS_Revised24= MAX(YR_TRANS_Revised24) /YR_TRANS_Revised25= MAX(YR_TRANS_Revised25) /YR_TRANS_Revised26= MAX(YR_TRANS_Revised26) /YR_TRANS_Revised27= MAX(YR_TRANS_Revised27) /YR_TRANS_Revised28= MAX(YR_TRANS_Revised28) /YR_TRANS_Revised29= MAX(YR_TRANS_Revised29) /YR_TRANS_Revised30= MAX(YR_TRANS_Revised30) /YR_TRANS_Revised31= MAX(YR_TRANS_Revised31) /YR_TRANS_Revised32= MAX(YR_TRANS_Revised32) /YR_TRANS_Revised33= MAX(YR_TRANS_Revised33). EXECUTE. LIST. From: Bruce Weaver [via
SPSSX Discussion] [mailto:[hidden email]]
What do you want the file to
look like when it has one row per ID? Take GRADE, for example. At
the moment, there are multiple values for each ID. Do you want to
preserve those raw values (e.g., in multiple columns)? Or do you want one
mean grade for each ID? Please provide more details. -- View message @ http://spssx-discussion.1045642.n5.nabble.com/Collapsing-multiple-records-by-unique-ID-tp3245589p3245631.html
|
RE: Collapsing multiple records by unique ID - Number of numbers in MIME From exceeds maximum threshold - Number of numbers in MIME From exceeds maximum threshold
|
Administrator
|
Re: Collapsing multiple records by unique ID
|
Comparing Models
|
|
Re: Comparing Models
|
|
Re: Comparing Models
|
|
Re: Comparing Models
|
|
Re: Comparing Models
|
|
Re: Comparing Models
|
|
Re: Comparing Models
|
|
Re: Comparing Models
|
|
Re: Comparing Models
|
|
Re: Comparing Models
|
|
| Free forum by Nabble | Edit this page |