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

How to create a wide dataset?

Reply
Occasional Learner
Posts: 1

How to create a wide dataset?

[ Edited ]

 

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.

 

Super User
Posts: 17,868

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. 

 

 

Respected Advisor
Posts: 4,654

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.

PG
PROC Star
Posts: 1,095

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?

 

Tom

Ask a Question
Discussion stats
  • 3 replies
  • 304 views
  • 2 likes
  • 4 in conversation