04-18-2016 10:24 PM - edited 04-18-2016 10:28 PM
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;
04-18-2016 10:37 PM
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.
04-18-2016 10:54 PM
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.
04-19-2016 09:21 AM
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?