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

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
Ksharp
Super User
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;

View solution in original post

7 REPLIES 7
ballardw
Super User

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.

David_Billa
Rhodochrosite | Level 12

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

ballardw
Super User

@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.

David_Billa
Rhodochrosite | Level 12
I want to rename Col1, Col2 variables names with meaningful name as I
mentioned in the initial post as well.
Reeza
Super User
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.
Ksharp
Super User
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;

sas-innovate-2024.png

Available on demand!

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

 

Register now!

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.

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
  • 7 replies
  • 647 views
  • 5 likes
  • 4 in conversation