DATA Step, Macro, Functions and more

How to - Transpose the Data Set by Grouping One Variable?

Accepted Solution Solved
Reply
Super Contributor
Posts: 381
Accepted Solution

How to - Transpose the Data Set by Grouping One Variable?

Hello everyone,

 

I would like to transpose the data set by grouping "Variable" variable. My desired output's image and my sample as below. I tried to use Proc Report for do this but because type of Suffix variable is character, I could not do it.

 

Can anybody help me, please?

 

Thank you

 

 

Data Have;
Length Variable $ 32 Dataset $ 32 Suffix $ 10 Value 8;
Infile Datalines Missover;
Input Variable Dataset Suffix Value;
Datalines;
Variable1 Dataset1 Suffix1 0.70
Variable2 Dataset1 Suffix1 0.40
Variable3 Dataset1 Suffix1 0.80
Variable1 Dataset2 Suffix2 0.45
Variable2 Dataset2 Suffix2 0.65
Variable3 Dataset2 Suffix2 0.35
Variable1 Dataset3 Suffix3 0.85
Variable2 Dataset3 Suffix3 0.90
Variable3 Dataset3 Suffix3 0.40
;
Run;

Proc Report Data=Have Nowd;
Column Variable Dataset,(Suffix Value);

Define Variable / Group ;
Define Dataset / Across;
Define Suffix / Analysis;
Define Value / Analysis;

Run;

Des.png

 


Accepted Solutions
Solution
‎08-09-2016 02:17 AM
Super User
Posts: 9,681

Re: How to - Transpose the Data Set by Grouping One Variable?

Check the MERGE skill proposed by me,Matt,Arthur.T :
http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf



Data Have;
Length Variable $ 32 Dataset $ 32 Suffix $ 10 Value 8;
Infile Datalines Missover;
Input Variable Dataset Suffix Value;
Datalines;
Variable1 Dataset1 Suffix1 0.70
Variable2 Dataset1 Suffix1 0.40
Variable3 Dataset1 Suffix1 0.80
Variable1 Dataset2 Suffix2 0.45
Variable2 Dataset2 Suffix2 0.65
Variable3 Dataset2 Suffix2 0.35
Variable1 Dataset3 Suffix3 0.85
Variable2 Dataset3 Suffix3 0.90
Variable3 Dataset3 Suffix3 0.40
;
Run;
data have;
 set have;
 by dataset;
 n+first.dataset;
run;
proc sql;
select distinct catt('have(where=(Dataset="',Dataset,'") 
       rename=(Suffix=',Dataset,' value=value',n,'))') 
       into : merge separated by ' '
 from have;
quit;

data want;
 merge &merge ;
 by variable;
 drop dataset n;
run;




View solution in original post


All Replies
Super User
Super User
Posts: 7,401

Re: How to - Transpose the Data Set by Grouping One Variable?

There are probably a million posts on here about reshaping data from long to wide and vice versa.  A quick google search gives many results, some of the top ones being:

http://www.lexjansen.com/nesug/nesug12/ff/ff01.pdf

http://www.ats.ucla.edu/stat/sas/modules/longtowide_data.htm

 

Proc report is not for transposing data.  Why do you want that kind of output?  If your programming further on it, then it is not a good structure for programming.

Super Contributor
Posts: 381

Re: How to - Transpose the Data Set by Grouping One Variable?

Thank you very much,

 

I think the best thing which I do, as below;

 

Proc Sort Data=Have; By Variable; Run;
Proc Transpose Data=Have Out=Want(Drop=_:);
By Variable;
ID Suffix;
Var Value;
Run;

Thank you

Super User
Posts: 17,828

Re: How to - Transpose the Data Set by Grouping One Variable?

Your transposing two variables so you need to do two proc transposes and merge the results. 

Or there's a macro A Better Way to Flip that will flip all at once. 

 

Or use a data step merge. 

Super Contributor
Posts: 381

Re: How to - Transpose the Data Set by Grouping One Variable?

 

Thank you very much

 

According to my question, what if my Have data set was as below;

 

Data Have;
Length Variable $ 32 Dataset $ 32 Suffix $ 10 Value 8;
Infile Datalines Missover;
Input Variable Dataset Suffix Value;
Datalines;
Variable1 Dataset1 Suffix1 0.70
Variable2 Dataset1 Suffix1 0.40
Variable3 Dataset1 Suffix1 0.80
Variable1 Dataset2 Suffix2 0.45
Variable2 Dataset2 Suffix2 0.65
Variable3 Dataset2 Suffix2 0.35
;
Run;
Proc Sort Data=Have; By Variable; Run;
Proc Transpose Data=Have Out=Want(Drop=_:);
By Variable;
ID Suffix;
Var Value;
Run;

/*Data Want2;*/
/*Set Want;*/
/*Suf1vsSuf2=((Suf1-Suf2)/Suf2);*/
/*Suf1vsSuf3=((Suf3-Suf1)/Suf1);*/
/*Suf2vsSuf3=((Suf3-Suf2)/Suf2);*/
/*Run;*/

Proc Sql;
Create Table Want2 As
Select *
,((Suf1-Suf2)/Suf2) As Suf1vsSuf2
,((Suf3-Suf1)/Suf1) As Suf1vsSuf3
,((Suf3-Suf2)/Suf2) As Suf2vsSuf3
From Want;
Quit;

Proc Report Data=Want2 Nowd;

Column Variable Suf1 Suf2 Suf3 Suf1vsSuf2 Suf1vsSuf3 Suf2vsSuf3;

Define Variable / Display;
Define Suf1 / Display;
Define Suf2 / Display;
Define Suf3 / Display;
Define Suf1vsSuf2 / Display;
Define Suf1vsSuf3 / Display;
Define Suf2vsSuf3 / Display;
Run;

I do not want to get errors, I want to build a dynamic structure, how can I prevent these errors?

 

For me Have data set is uncertain, it can change.

 

Have data set can include Suffix1 and Suffix2 or Suffix2 and Suffix3 or Suffix1 and Suffix or can include none of them. I need to build this structure depending on posibility of changes Have data set.

 

Can anybody help me, please? I need some direction and guidance Smiley Happy

 

Thank you

Super User
Posts: 17,828

Re: How to - Transpose the Data Set by Grouping One Variable?

This is different than your initial question. If your initial question has been answered then mark this solved and start a new one.

 

I believe this was a previous question of yours that was already discussed as well.

Solution
‎08-09-2016 02:17 AM
Super User
Posts: 9,681

Re: How to - Transpose the Data Set by Grouping One Variable?

Check the MERGE skill proposed by me,Matt,Arthur.T :
http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf



Data Have;
Length Variable $ 32 Dataset $ 32 Suffix $ 10 Value 8;
Infile Datalines Missover;
Input Variable Dataset Suffix Value;
Datalines;
Variable1 Dataset1 Suffix1 0.70
Variable2 Dataset1 Suffix1 0.40
Variable3 Dataset1 Suffix1 0.80
Variable1 Dataset2 Suffix2 0.45
Variable2 Dataset2 Suffix2 0.65
Variable3 Dataset2 Suffix2 0.35
Variable1 Dataset3 Suffix3 0.85
Variable2 Dataset3 Suffix3 0.90
Variable3 Dataset3 Suffix3 0.40
;
Run;
data have;
 set have;
 by dataset;
 n+first.dataset;
run;
proc sql;
select distinct catt('have(where=(Dataset="',Dataset,'") 
       rename=(Suffix=',Dataset,' value=value',n,'))') 
       into : merge separated by ' '
 from have;
quit;

data want;
 merge &merge ;
 by variable;
 drop dataset n;
run;




Super Contributor
Posts: 381

Re: How to - Transpose the Data Set by Grouping One Variable?

Thank your very much @Ksharp,

 

Okay, I'm going to open a new discussio @Reeza

 

Thank you

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 352 views
  • 1 like
  • 4 in conversation