Help with transpose problem

Accepted Solution Solved
Reply
Super Contributor
Posts: 371
Accepted Solution

Help with transpose problem

Hi Everyone,

I have a data that report result for different method for different peirod as below.

I want to spread the report horizontally by method.

I wonder if there is any way to do it.

Thank you.

HHC

 

 

data have;
input period method result1 result2;
cards;
1 1 0 2
1 2 2 5
1 3 5 8
2 1 3 3
2 2 3 6
2 3 6 9

;
run;

 

Data Want look like that:

method     period1_result1 period1_result2      period2_result1 period2_result2
1
2
3


Accepted Solutions
Solution
‎02-10-2016 12:06 AM
Super User
Posts: 17,819

Re: Help with transpose problem

Yes, transpose once with result1 and transpose a second time with result2 then merge the two.

 

See example 2 here:

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

 

Or if you're feeling adventurous you can try this macro:

http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset

 

 

View solution in original post


All Replies
Super User
Posts: 17,819

Re: Help with transpose problem

Proc tranpsose X 2 (once for each variable) and then merge by period/method.

Super Contributor
Posts: 371

Re: Help with transpose problem

I am not clear about your suggestion.

You mean transpose for each result1, result2 twice?

Solution
‎02-10-2016 12:06 AM
Super User
Posts: 17,819

Re: Help with transpose problem

Yes, transpose once with result1 and transpose a second time with result2 then merge the two.

 

See example 2 here:

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

 

Or if you're feeling adventurous you can try this macro:

http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset

 

 

Super User
Posts: 5,256

Re: Help with transpose problem

You should be to do report by using a reporting procudere like REPORT or TABULATE.
Data never sleeps
Super Contributor
Posts: 371

Re: Help with transpose problem

data long2; 
  input ID year faminc spend ; 
cards; 
1 96 40000 38000 
1 97 40500 39000 
1 98 41000 40000 
2 96 45000 42000 
2 97 45400 43000 
2 98 45800 44000 
3 96 75000 70000 
3 97 76000 71000 
3 98 77000 72000 
; 
run ;

/*spread into 3 rows by ID*/
proc transpose data=long2 out=widef prefix=faminc;
   by ID;
   id year;		*will be use to name the prefix;
   var faminc;
run;

proc transpose data=long2 out=wides prefix=spend;
   by ID;
   id year;
   var spend;
run;

data wide2;
    merge  widef(drop=_name_) wides(drop=_name_);
    by ID;
run;


/*spread into 1 rows*/
proc transpose data=long2 out=wide_faminc prefix=faminc;
   id ID year;
   var faminc;
run;
data wide_faminc;set wide_faminc;
_name_="a";run;

proc transpose data=long2 out=wide_spend prefix=spend;
   id ID year;
   var spend;
run;
data wide_spend;set wide_spend;
_name_="a";run;

proc sql;
create table wide3
as select * from wide_spend as a join wide_faminc as b
on a._name_=b._name_;quit;
☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 336 views
  • 0 likes
  • 3 in conversation