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
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.
Below paper from et al. should give you all you need.
http://support.sas.com/resources/papers/proceedings13/538-2013.pdf
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.
Adding to Arthur's comments, you could also do this with PROC SQL via a program in EG.
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
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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.