Desktop productivity for business analysts and programmers

Restructuring data set

Accepted Solution Solved
Reply
Contributor
Posts: 73
Accepted Solution

Restructuring data set

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:

DateRel_DayCampusBlendedPaidUnpaid
1/26/201419Central57.7757.270.5
1/26/201419North89.8487.172.67
1/26/201419South67.265.671.53

Want:

DateRel_DayCentral_BlendedCentral_PaidCentral_UnpaidNorth_BlendedNorth_PaidNorth_UnpaidSouth_BlendedSouth_PaidSouth_Unpaid
1/26/20141957.7757.270.589.8487.172.6767.265.671.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


Accepted Solutions
Solution
‎01-27-2014 05:07 PM
Esteemed Advisor
Posts: 7,296

Re: Restructuring data set

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.

View solution in original post


All Replies
Respected Advisor
Posts: 3,837

Re: Restructuring data set

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

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

Solution
‎01-27-2014 05:07 PM
Esteemed Advisor
Posts: 7,296

Re: Restructuring data set

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.

Super Contributor
Posts: 307

Re: Restructuring data set

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

Esteemed Advisor
Posts: 7,296

Re: Restructuring data set

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

Contributor
Posts: 73

Re: Restructuring data set

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!

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 541 views
  • 0 likes
  • 4 in conversation