- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hello,
I'm looking for help in restructing data in the format specified in Data.PNG to the format specified in Anticipated output.PNG
Basically I just need one unique id per record. Kindly help.
Regards,
Bhuvana
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Actually, given your data, it would take a data step (to create a counter field), proc sort, and then at least two uses of proc transpose, one to make the file long, then another to make the file wide.
An alternative that does it all in one step is to use a macro that a number of us wrote some years ago. You can download the macro (it's free) at: http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset
If you download and run that macro, the following will accomplish what you want:
%transpose(data=have, out=want, by=id name country, var=complaints_description complaint_id, copy=comments, sort=yes, guessingrows=1000)
Art, CEO, AnalystFinder.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Do you only want one comment variable or one for each complaint? Regardless, all you need to use is proc transpose.
Art, CEO, AnalystFinder.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Yes, one comment variable for all the complaints in a single row. Thanks for quick response, I will try this proc and update the thread.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Actually, given your data, it would take a data step (to create a counter field), proc sort, and then at least two uses of proc transpose, one to make the file long, then another to make the file wide.
An alternative that does it all in one step is to use a macro that a number of us wrote some years ago. You can download the macro (it's free) at: http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset
If you download and run that macro, the following will accomplish what you want:
%transpose(data=have, out=want, by=id name country, var=complaints_description complaint_id, copy=comments, sort=yes, guessingrows=1000)
Art, CEO, AnalystFinder.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
A single DATA step with two arrays would work, but requires knowing a little more about the data. Is there a maximum of four entries per customer? That maximum tells you how many elements to set up in each array. Other than that, though, the DATA step is pretty straightforward array processing:
- Use sorted data with a BY statement
- Set all array elements to missing when beginning a new customer's data
- Create a counter that tracks which observation number are on working with for the current customer
- Populate array elements (remember to retain them)
- Output when reaching the last observation per customer
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for the reply. I was able to get this issue sorted by using the transpose macro given above but would like to try other coding alternatives. To answer your question, there are varying number of entries per customers. In such cases, should we give the maximum value as array dimension? Appreciate the help.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Yes, to build a datastep solution that uses arrays:
1.First, you have to insure that your data is in the proper order (i.e., include a proc sort if necessary)
2.Know the maximum number of replications
3.Create (and retain) arrays for each of the variables you want to transpose (insuring that you have them correctly set to accept character/numeric data)
4,Use by processing to control a counter that indicates replications within an id (i.e., when first.id)
5.Use by processing to know when to output the record (i.e., last.id)
That's why I prefer the transpose macro. the guessingrows parameter lets you decide how many records need to be reviewed in order to determine the maximum arrays needed, the sort parameter does the sort for you and, if you are transposing both character and numeric data, it keeps their original formats (which proc transpose doesn't do).
Art, CEO, AnalystFinder.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Absoutely!
Transpose macro worked like magic. I think I'm gonna use it on several occasions in future. Appreciate your help!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks a lot for coming to the rescue :). Have a good weekend!