topic Re: transpose multiple columns and getting output in columns in SAS Programming

transpose multiple columns and getting output in columns

deega — Fri, 10 Mar 2017 05:50:59 GMT

Hi All,

I want to transpose the following dataset

id	d	amt
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

and I am trying to get following output

	d1	d2	d3	d4	d5	a1	a2	a3	a4	a5
x001	12					1000
x002	10	11	12			1000	2000	3000
x003	13	15				1000	3000
x004	23					1000
x005	13	14	16	17	18	4000	2000	3000	4000	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;

Re: transpose multiple columns and getting output in columns

Reeza — Fri, 10 Mar 2017 06:52:53 GMT

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

Re: transpose multiple columns and getting output in columns

Reeza — Fri, 10 Mar 2017 06:52:55 GMT

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

Re: transpose multiple columns and getting output in columns

deega — Fri, 10 Mar 2017 07:12:37 GMT

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

Re: transpose multiple columns and getting output in columns

Ksharp — Fri, 10 Mar 2017 08:26:05 GMT

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;