BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
turcay
Lapis Lazuli | Level 10

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Lapis Lazuli | Level 10

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

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
Lapis Lazuli | Level 10

 

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

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
Super User
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
Lapis Lazuli | Level 10

Thank your very much @Ksharp,

 

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

 

Thank you

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 1239 views
  • 1 like
  • 4 in conversation