topic Re: Need assistance in restructuring data in SAS Programming

Need assistance in restructuring data

Bhuvaneswari — Fri, 31 Mar 2017 14:09:06 GMT

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

Re: Need assistance in restructuring data

art297 — Fri, 31 Mar 2017 14:14:02 GMT

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

Re: Need assistance in restructuring data

Bhuvaneswari — Fri, 31 Mar 2017 14:17:11 GMT

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.

Re: Need assistance in restructuring data

art297 — Fri, 31 Mar 2017 14:41:11 GMT

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

Re: Need assistance in restructuring data

rogerjdeangelis — Fri, 31 Mar 2017 15:30:16 GMT

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?

Re: Need assistance in restructuring data

Astounding — Fri, 31 Mar 2017 15:46:49 GMT

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

Re: Need assistance in restructuring data

Bhuvaneswari — Fri, 31 Mar 2017 16:03:09 GMT

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.

Re: Need assistance in restructuring data

Bhuvaneswari — Fri, 31 Mar 2017 16:04:03 GMT

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

Re: Need assistance in restructuring data

art297 — Fri, 31 Mar 2017 16:17:50 GMT

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

Re: Need assistance in restructuring data

Bhuvaneswari — Fri, 31 Mar 2017 17:36:55 GMT

Absoutely!

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