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

[turcay]

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;

[RW9]

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.

[turcay]

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

[Reeza]

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. 

[turcay]

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 🙂

Thank you

[Reeza]

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.

[Ksharp · Accepted solution]

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;

[turcay]

Thank your very much @Ksharp,

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

Thank you