topic Re: Transpose one variable to create multiple variables in SAS Programming

Transpose one variable to create multiple variables

David_Billa — Tue, 18 Aug 2020 16:39:15 GMT

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

Re: Transpose one variable to create multiple variables

ballardw — Tue, 18 Aug 2020 16:54:49 GMT

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.

Re: Transpose one variable to create multiple variables

Reeza — Tue, 18 Aug 2020 16:57:57 GMT

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

Re: Transpose one variable to create multiple variables

David_Billa — Tue, 18 Aug 2020 17:26:49 GMT

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

Re: Transpose one variable to create multiple variables

ballardw — Tue, 18 Aug 2020 18:27:40 GMT

@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-data-AKA-generate/ta-p/258712 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.

Re: Transpose one variable to create multiple variables

David_Billa — Tue, 18 Aug 2020 18:47:05 GMT

I want to rename Col1, Col2 variables names with meaningful name as I
mentioned in the initial post as well.

Re: Transpose one variable to create multiple variables

Reeza — Tue, 18 Aug 2020 19:03:31 GMT

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.

Re: Transpose one variable to create multiple variables

Ksharp — Wed, 19 Aug 2020 12:51:16 GMT

data x;
input (Value	      Currency	         Segment) ( : $40.);
cards;
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
;
proc transpose data=x out=temp(drop=_name_);
var value;
run;
data want;
 set x(keep=Currency  Segment);
 if _n_=1 then set temp;
run;