Restructuring data set

[noobs]

Hello:

I am interested in restructuring dataset such that (3 x 6 matrix) transforms into (1 x 11 matrix). Essentially, looking for converting narrow dataset into wide dataset, number of columns in wide data set depends on distinct values in variable "Campus".

Have:

Date	Rel_Day	Campus	Blended	Paid	Unpaid
1/26/2014	19	Central	57.77	57.27	0.5
1/26/2014	19	North	89.84	87.17	2.67
1/26/2014	19	South	67.2	65.67	1.53

Want:

Date	Rel_Day	Central_Blended	Central_Paid	Central_Unpaid	North_Blended	North_Paid	North_Unpaid	South_Blended	South_Paid	South_Unpaid
1/26/2014	19	57.77	57.27	0.5	89.84	87.17	2.67	67.2	65.67	1.53

Suggestions on how to achieve this using either proc TRANSPOSE or any other method is SAS EG will be truly appreciated.

Thanks for your time and attention,

Dhanashree

[Patrick]

Below paper from et al. should give you all you need.

http://support.sas.com/resources/papers/proceedings13/538-2013.pdf

[art297 · Accepted solution]

While you could definitely accomplish the task with the macro that mentioned, you can also do it in EG .. just takes more steps.

1st, you would select the transpose task and transpose blended, paid and unpaid, using date, rel_day and campus as by variables.

2nd, you would use query builder to create a new table from the result of step 1.  You would select date, rel_day and column1 as variables, along with computing a new variable using the complex expression (I think it is called) method and the expression would be: catx('_',campus,source) and calling the result source

3rd, you would use the transpose task using column1 as the variable to transpose, date and rel_day as by variables and source as the name variable    Under the options, you would want to make sure that use prefix is NOT checked.

If I correctly remembered all of the above steps, that will result in the file you want, other than it will include an extra column called source that you will have to delete in yet another step.

The macro, methinks, would be a lot easier, but I'm not a big advocate of EG to begin with, thus am definitely biased.

[Fugue]

Adding to Arthur's comments, you could also do this with PROC SQL via a program in EG.

[art297]

One last comment.  If you decide to try the suggestion made by , rather than using the code in the paper, use the most recent version.  i.e.,

1. download the code from: http://www.sascommunity.org/mwiki/images/b/be/BB-07-2013.sas

2, open a program node and paste the code there

3. assuming your data is in work.have and you want work.want then, at the very bottom of the code add:

%transpose(data=work.have,

           out=work.want,

           by=date rel_day,

           id=campus,

           var=blended paid unpaid,

           var_first=no,

           delimiter=_,

           sort=yes)

4. then click on run

That should create your desired output file

P.S. The paper has been updated as well.  The current version can be found at: A Better Way to Flip (Transpose) a SAS Dataset - sasCommunity

[noobs]

That was insightful, thanks so much Arthur on your input. I have resolved this issue by breaking down the step into multiple nodes and using PROC TRANSPOSE on each sub dataset. Thanks again!