Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- Transpose one variable to create multiple variables

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 08-18-2020 12:39 PM
(646 views)

Appereciate if someone of you help me understand to how to the transpose the data based on variable? I just look for the hint.

I've a data as below.

Value Currency Segment 795.144,06 EUR 7183_AC_AS3 804.036,54 EUR 7183_AC_AS3 842.278,32 EUR 7183_AC_AS3 817.077,24 EUR 7183_AC_AS3

Now I want to create the dataset as below.

Currency Segment Value A Value B Value C Value D EUR 7183_AC_AS3 795.144,06 804.036,54 842.278,32 817.077,24 EUR 7183_AC_AS3 795.144,06 804.036,54 842.278,32 817.077,24 EUR 7183_AC_AS3 795.144,06 804.036,54 842.278,32 817.077,24 EUR 7183_AC_AS3 795.144,06 804.036,54 842.278,32 817.077,24

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

7 REPLIES 7

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

A basic transpose

Proc transpose data=have out=trans; by currency segment; var value; run;

(requires input data set to be sorted by currency and segment as usual for BY groups)

of that data would result in something like:

Currency Segment Col1 Col2 Col3 Col4 EUR 7183_AC_AS3 795.144,06 804.036,54 842.278,32 817.077,24

So why would you need 4 identical rows of values?

Do you have more than one segment? Would each one of those have exactly 4 values and 4 rows or differing numbers of values?

It would be easy in another data step to create multiple identical rows from that transposed data but there may be some rules you haven't provided yet.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

In real like I have multiple variables alongside the provide variables in the initial post. To answer your questions,

So why would you need 4 identical rows of values? *It will not be indentical if I consider the other few variables which I didn't mention in the post.*

Do you have more than one segment? *yes, I have more than one Segment in real data*

Would each one of those have exactly 4 values and 4 rows or differing numbers of values? *not really, there will be differing numbers of values*

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

@David_Billa wrote:

In real like I have multiple variables alongside the provide variables in the initial post. To answer your questions,

So why would you need 4 identical rows of values?

It will not be indentical if I consider the other few variables which I didn't mention in the post. Then provide a more realistic example including some of the other variables and what role they play in the final output.Do you have more than one segment?

yes, I have more than one Segment in real data

Would each one of those have exactly 4 values and 4 rows or differing numbers of values?

not really, there will be differing numbers of values. Provide more realistic example of the data and the matching expected output, include different segments.

Likely what this will resolve to would a transpose similar to the one I show above and then joining that result to your existing data. But we do need to know what the input/output looks like;

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

I want to rename Col1, Col2 variables names with meaningful name as I

mentioned in the initial post as well.

mentioned in the initial post as well.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

ID and The IDLABEL option within PROC TRANSPOSE support dynamic naming and labeling.

Post an example that's reflective of your situation, doesn't need to be real data but more representative it is the better answer you'll get.

Post an example that's reflective of your situation, doesn't need to be real data but more representative it is the better answer you'll get.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Your example isn't an example of transposing and if you had to do that, it wouldn't be a transpose either. Below are the transpose tutorials I find the most useful for beginners.

Transposing data tutorials:

Long to Wide:

https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

https://stats.idre.ucla.edu/sas/modules/reshaping-data-long-to-wide-using-the-data-step/

Wide to Long:

https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/

https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/

And sometimes a double transpose is needed for extra wide data sets:

https://gist.github.com/statgeek/2321b6f62ab78d5bf2b0a5a8626bd7cd

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

**Available on demand!**

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.