SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How to create a wide dataset?

Occasional Learner
Posts: 1

How to create a wide dataset?

[ Edited ]




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 $;
A100 XYZ 1999 CA
A100 XYZ 2010 TX
A200 PQR 2013 IL
A200 XYZ 2015 IL

Output needed:

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

Thank you.


Super User
Posts: 23,224

Re: How to create a wide dataset?

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. 



Esteemed Advisor
Posts: 5,474

Re: How to create a wide dataset?

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.

Posts: 1,286

Re: How to create a wide dataset?

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?



Ask a Question
Discussion stats
  • 3 replies
  • 4 in conversation