Need assistance in restructuring data

[Bhuvaneswari]

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

[art297]

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

[Bhuvaneswari]

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.

[art297 · Accepted solution]

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

[rogerjdeangelis]

Without data I hesitate to comment. However a single datastep with two arrays might work? May need to presort? Process four records and output 1?

[Astounding]

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

[Bhuvaneswari]

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.

[art297]

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

[Bhuvaneswari]

Absoutely! 

Transpose macro worked like magic. I think I'm gonna use it on several occasions in future. Appreciate your help!

[Bhuvaneswari]

Thanks a lot for coming to the rescue :). Have a good weekend!