topic Re: Collapse multiple observations into few by id: retaining all information in SAS Programming

Collapse multiple observations into few by id: retaining all information

mlrenner — Thu, 16 Jul 2015 21:40:19 GMT

I Have data like this

data=bookings

ID Arrest_charge Date

1 PC187 3/20/15

1 WIC 122.4 6/15/15

2 PC 71 6/30/12

2 PC988 6/30/14

2 PC141 7/14/15

3 VC340 8/9/10

4 PC251.1(A) 8/17/11

4 VC241(B) 9/23/11

I want to collapse the data so that each unique ID is one observation, but retain all the information creating new variables that extend horizontally depending on how many arrest records there are for each unique ID. Like this.

ID Arrest_charge1 Date1 arrest_charge2 date2 arrest_charge3 Date3

1 PC187 3/20/15 WIC 122.4 6/15/15 . .

2 PC71 6/30/12 PC988 6/30/14 PC141 7/14/15

3 VC340 8/9/10 . . . .

4 PC251.1(A) 8/17/11 VC241(B) 9/23/11 . .

. or dot indicates a value that is missing because there was not that many charges and dates per booking (i know . is for numerical values and im working with codes.

Please help me im lost. Do i need to write a macro if i dont want to create variables for the max number of charges and dates? I tried a bunch of things but none seem to retain all info just one of the observations.

thanks.

-Matt

Re: Collapse multiple observations into few by id: retaining all information

ballardw — Thu, 16 Jul 2015 23:02:14 GMT

Here's a recent example of something similar:

https://communities.sas.com/thread/81958

But HOW will you use the format? It may be easier to leave as original.

Re: Collapse multiple observations into few by id: retaining all information

PGStats — Fri, 17 Jul 2015 02:44:28 GMT

I agree with . Keep the data in long format and transform last for reporting purposes, if needed

data report;

length Charges $80;

do until(last.id);

set bookings; by id;

charges = catx("/ ", charges,

catt(Arrest_Charge, " [", put(date, yymmdd10.), "]"));

end;

keep id charges;

run;

proc print data=report noobs; var id charges; run;

Re: Collapse multiple observations into few by id: retaining all information

ChrisNZ — Fri, 17 Jul 2015 10:37:12 GMT

data BOOKINGS;

input ID ARREST_CHARGE :& $10. ARREST_DATE : mmddyy.;

cards;

1 PC187 3/20/15

1 WIC 122.4 6/15/15

2 PC 71 6/30/12

2 PC988 6/30/14

2 PC141 7/14/15

3 VC340 8/9/10

4 PC251.1(A) 8/17/11

4 VC241(B) 9/23/11

run;

data WANT;

array DATE [10] 8;

array CHARGE [10] $10;

retain DATE: CHARGE:;

set BOOKINGS;

by ID;

if first.ID then call missing(I, of DATE

, of CHARGE

);

I+1 ;

DATE =ARREST_DATE;

CHARGE=ARREST_CHARGE;

if last.ID then output;

run;

proc print ;

var ID DATE1 CHARGE1 DATE2 CHARGE2 DATE3 CHARGE3;

format DATE: date9.;

run;

1	1	20MAR2015	PC187	15JUN2015	WIC 122.4	.
2	2	30JUN2012	PC 71	30JUN2014	PC988	14JUL2015	PC141
3	3	09AUG2010	VC340	.		.
4	4	17AUG2011	PC251.1(A)	23SEP2011	VC241(B)	.