BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
noobs
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

5 REPLIES 5
Patrick
Opal | Level 21

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

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

art297
Opal | Level 21

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
Quartz | Level 8

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

art297
Opal | Level 21

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
Calcite | Level 5

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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