How to create a wide dataset?

[sandyrcl]

Hi,

I have a dataset with many ID's and each id has multiple events(models in the case of the sample data). I want the output data set to have one observation per subject, count the maximum events per id per model and create a flagging variable(a,b, c and so on) for each occurence of the event and the corresponding values of year and state for that event. Any help would be appreciated. Thanks in advance.

Sample data:

data test;
input id $ model $ year state $;
datalines;
A100 XYZ 1999 CA
A100 XYZ 2010 TX
A200 PQR 2013 IL
A200 XYZ 2015 IL
;
RUN

Output needed:

DATA TEST2;
INPUT id $ axyz bxyz xyzayr xyzbyr xyzast $ xyzbst $ apqr pqrayr pqrast $ ;
datalines;
A100 1 1 1999 2010 CA TX . . .
A200 1 . 2015 . IL . 1 2013 IL
;
RUN;

Thank you.

[Reeza]

Thats gonna be hell to work with after. 

That being said it looks like two proc transposes that are then merged by ID. You can calculate the 1's afterwards by scanning the values. 

[PGStats]

Please tell us what you intend to achieve with such a data structure. Give us a chance to propose an alternative that avoids the complications inherent to this route.

[TomKari]

I vociferously support the previous two posters in recommending that you look for an alternative.

Are you familiar with the concept of a summary dataset, that would have your summary information at the id level, that you could then join to a lower level dataset using common keys?

Tom