DATA Step, Macro, Functions and more

transpose multiple columns and getting output in columns

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

transpose multiple columns and getting output in columns

Hi All,

I want to transpose the following dataset 

iddamt
x001121000
x002101000
x002112000
x002123000
x003131000
x003153000
x004231000
x005134000
x005142000
x005163000
x005174000
x005181000

 

and I am trying to get following output

 d1d2d3d4d5a1a2a3a4a5
x00112    1000    
x002101112  100020003000  
x0031315   10003000   
x00423    1000    
x00513141617184000200030004000

1000

 

However, if I write the following program I get values of different columns in different rows.

 

proc transpose data=Y out=check  ;
id ID;
var d amt;
run;


Accepted Solutions
Solution
‎03-10-2017 02:12 AM
Contributor
Posts: 71

Re: transpose multiple columns and getting output in columns

I tried the same. Transposed and then merged. I was wondering if there is any other option.

View solution in original post


All Replies
Super User
Posts: 19,772

Re: transpose multiple columns and getting output in columns

I think you need to do transposes and then do a merge of those two datasets. 

Or you can use a really handy macro written by some users on here called 'A better way to flip' 

 

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

Super User
Posts: 19,772

Re: transpose multiple columns and getting output in columns

I think you need to do transposes and then do a merge of those two datasets. 

Or you can use a really handy macro written by some users on here called 'A better way to flip' 

 

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

Solution
‎03-10-2017 02:12 AM
Contributor
Posts: 71

Re: transpose multiple columns and getting output in columns

I tried the same. Transposed and then merged. I was wondering if there is any other option.

Super User
Posts: 10,020

Re: transpose multiple columns and getting output in columns

1)
Yes. there are another option. MERGE skill proposed by Me,Arthur,Matt.


http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf


data have;
infile cards expandtabs truncover;
input id $ d	amt;
cards;
x001	12	1000
x002	10	1000
x002	11	2000
x002	12	3000
x003	13	1000
x003	15	3000
x004	23	1000
x005	13	4000
x005	14	2000
x005	16	3000
x005	17	4000
x005	18	1000
;
run;
data have;
 set have;
 by id;
 if first.id then n=0;
 n+1;
run;
proc sql;
select distinct catt('have(where=(n=',n,') rename=(d=d',n,' amt=amt',n,'))')
 into : merge separated by ' '
  from have;
quit;

data want;
 merge &merge;
 by id;
 drop n;
run;
proc print;run;



2) Or another option is IDGROUPS


data have;
infile cards expandtabs truncover;
input id $ d	amt;
cards;
x001	12	1000
x002	10	1000
x002	11	2000
x002	12	3000
x003	13	1000
x003	15	3000
x004	23	1000
x005	13	4000
x005	14	2000
x005	16	3000
x005	17	4000
x005	18	1000
;
run;
proc sql;
select max(n) into :n 
 from 
 (select count(*) as n from have group by id);
quit;
proc summary data=have;
by id;
output out=want idgroups(out[&n] (d amt)=);
run;
proc print;run;


☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 264 views
  • 1 like
  • 3 in conversation