DATA Step, Macro, Functions and more

Need assistance in restructuring data

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

Need assistance in restructuring data

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

 


Anticipated output.PNGData.PNG

Accepted Solutions
Solution
‎03-31-2017 11:56 AM
PROC Star
Posts: 7,492

Re: Need assistance in restructuring data

[ Edited ]
Posted in reply to Bhuvaneswari

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

 

View solution in original post


All Replies
PROC Star
Posts: 7,492

Re: Need assistance in restructuring data

Posted in reply to Bhuvaneswari

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

 

Contributor
Posts: 22

Re: Need assistance in restructuring data

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.

Solution
‎03-31-2017 11:56 AM
PROC Star
Posts: 7,492

Re: Need assistance in restructuring data

[ Edited ]
Posted in reply to Bhuvaneswari

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

 

Valued Guide
Posts: 505

Re: Need assistance in restructuring data

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?
Super User
Posts: 5,516

Re: Need assistance in restructuring data

Posted in reply to rogerjdeangelis

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
Contributor
Posts: 22

Re: Need assistance in restructuring data

Posted in reply to Astounding

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.

PROC Star
Posts: 7,492

Re: Need assistance in restructuring data

Posted in reply to Bhuvaneswari

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

 

Contributor
Posts: 22

Re: Need assistance in restructuring data

Absoutely! 

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

Contributor
Posts: 22

Re: Need assistance in restructuring data

Thanks a lot for coming to the rescue Smiley Happy. Have a good weekend!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 208 views
  • 5 likes
  • 4 in conversation