topic Re: How may I "transpose" a dataset so that it goes from multi-row, multi-column, to wide in SAS Programming

How may I "transpose" a dataset so that it goes from multi-row, multi-column, to wide single-row

AllSoEasy — Wed, 29 Jan 2020 00:31:25 GMT

Hello --

I have a dataset with multiple rows and columns, as such (Have):

SingleRate	SingleBracket	MarriedRate	MarriedBracket
10%	$0	10%	$0
12%	$13,851	12%	$9,701
22%	$52,851	22%	$39,476
24%	$84,201	24%	$84,201
32%	$160,701	32%	$160,726
35%	$204,101	35%	$204,101
37%	$510,301	37%	$306,176

And I want to transpose it so that every cell turns into it's own column, and the columns names are just suffixed with numbers (or something to distinctly signify them): (Want😞

SingleRate1	SingleRate2	SingleRate3	SingleRate4	SingleRate5	SingleRate6	SingleRate7	SingleBracket1	SingleBracket2	SingleBracket3	SingleBracket4	SingleBracket5	SingleBracket6	SingleBracket7	MarriedRate1	MarriedRate2	MarriedRate3	MarriedRate4	MarriedRate5	MarriedRate6	MarriedRate7	MarriedBracket1	MarriedBracket2	MarriedBracket3	MarriedBracket4	MarriedBracket5	MarriedBracket6	MarriedBracket7
10%	12%	22%	24%	32%	35%	37%	$0	$13,851	$52,851	$84,201	$160,701	$204,101	$510,301	10%	12%	22%	24%	32%	35%	37%	$0	$9,701	$39,476	$84,201	$160,726	$204,101	$306,176

I have been trying to use proc transpose with pretty much every permutation of by/id/var statements I can think of, but cannot seem to achieve this result. Might anyone have any ideas?

Thank you very much.

-Ryan

Re: How may I "transpose" a dataset so that it goes from multi-row, multi-column, to wide

novinosrin — Wed, 29 Jan 2020 01:46:14 GMT


data have;
input (SingleRate	SingleBracket	MarriedRate	MarriedBracket) (:$10.);
cards;
10%	$0	10%	$0
12%	$13,851	12%	$9,701
22%	$52,851	22%	$39,476
24%	$84,201	24%	$84,201
32%	$160,701	32%	$160,726
35%	$204,101	35%	$204,101
37%	$510,301	37%	$306,176 
;

data temp;
 set have;
 array 	t SingleRate--MarriedBracket;
 n=_n_;
 do over t;
  vn=put(vname(t),32.);
  v=t;
 output;
 end;
 keep vn v n;
run;

proc transpose data=temp out=want(drop=_:) delimiter=_;
id  vn n;
var v;
run;

Re: How may I "transpose" a dataset so that it goes from multi-row, multi-column, to wide

novinosrin — Wed, 29 Jan 2020 01:55:39 GMT

And another one

data have;
input (SingleRate	SingleBracket	MarriedRate	MarriedBracket) (:$10.);
cards;
10%	$0	10%	$0
12%	$13,851	12%	$9,701
22%	$52,851	22%	$39,476
24%	$84,201	24%	$84,201
32%	$160,701	32%	$160,726
35%	$204,101	35%	$204,101
37%	$510,301	37%	$306,176 
;
proc sql noprint;
select count(*) as obs 	into :obs
 from have;
quit; 

proc summary nway data=have missing; 
 output  out = want(drop=_type_ _freq_) 
 idgroup(out[&obs](SingleRate--MarriedBracket)=) ;
run;

Re: How may I "transpose" a dataset so that it goes from multi-row, multi-column, to wide

AllSoEasy — Wed, 29 Jan 2020 14:27:22 GMT

Thank you very much novinosrin!

One quick follow up -- is it possible to extrapolate this solution to do a similar thing with a 'by group'? i.e.

Have:

byVar	SingleRate	SingleBracket	MarriedRate	MarriedBracket
Group1	10%	$0	10%	$0
Group1	12%	$13,851	12%	$9,701
Group1	22%	$52,851	22%	$39,476
Group1	24%	$84,201	24%	$84,201
Group1	32%	$160,701	32%	$160,726
Group1	35%	$204,101	35%	$204,101
Group1	37%	$510,301	37%	$306,176
Group2	10%	$3,000	10%	$3,200
Group2	12%	$15,851	12%	$11,701
Group2	22%	$54,851	22%	$41,476
Group2	24%	$86,201	24%	$86,201
Group2	32%	$162,701	32%	$162,726
Group2	35%	$206,101	35%	$206,101
Group2	37%	$512,301	37%	$308,176

want:

byVar

SingleRate1

SingleRate2

SingleRate3

SingleRate4

SingleRate5

SingleRate6

SingleRate7

SingleBracket1

SingleBracket2

SingleBracket3

SingleBracket4

SingleBracket5

SingleBracket6

SingleBracket7

MarriedRate1

MarriedRate2

MarriedRate3

MarriedRate4

MarriedRate5

MarriedRate6

MarriedRate7

MarriedBracket1

MarriedBracket2

MarriedBracket3

MarriedBracket4

MarriedBracket5

MarriedBracket6

MarriedBracket7

Group1

10%

12%

22%

24%

32%

35%

37%

$13,851

$52,851

$84,201

$160,701

$204,101

$510,301

10%

12%

22%

24%

32%

35%

37%

$9,701

$39,476

$84,201

$160,726

$204,101

$306,176

Group2

10%

12%

22%

24%

32%

35%

37%

$3,000

$15,851

$54,851

$86,201

$162,701

$206,101

$512,301

10%

12%

22%

24%

32%

35%

37%

$3,200

$11,701

$41,476

$86,201

$162,726

$206,101

$308,176

I have been trying to adapt your code, which does work perfectly for the first case; I thought it would be easy to adapt to a by-group solution once I had that solved, but it actually isn't obvious to me how to do this. Though I am not super familiar with array work in SAS, so apologies for me ignorance there.

Re: How may I "transpose" a dataset so that it goes from multi-row, multi-column, to wide

novinosrin — Wed, 29 Jan 2020 14:33:40 GMT

Sure @AllSoEasy

Please try

data have;
input (byVar SingleRate	SingleBracket	MarriedRate	MarriedBracket) (:$10.);
cards;
Group1	10%	$0	10%	$0
Group1	12%	$13,851	12%	$9,701
Group1	22%	$52,851	22%	$39,476
Group1	24%	$84,201	24%	$84,201
Group1	32%	$160,701	32%	$160,726
Group1	35%	$204,101	35%	$204,101
Group1	37%	$510,301	37%	$306,176
Group2	10%	$3,000	10%	$3,200
Group2	12%	$15,851	12%	$11,701
Group2	22%	$54,851	22%	$41,476
Group2	24%	$86,201	24%	$86,201
Group2	32%	$162,701	32%	$162,726
Group2	35%	$206,101	35%	$206,101
Group2	37%	$512,301	37%	$308,176
;

proc sql noprint; 
 select max(obs) into :obs 
 from  (select count(*) as obs from have group by byVar );
quit;
proc summary nway data=have missing; 
 class byvar;
 output  out = want(drop=_type_ _freq_) 
 idgroup(out[&obs](SingleRate--MarriedBracket)=) ;
run;

Re: How may I "transpose" a dataset so that it goes from multi-row, multi-column, to wide

PaigeMiller — Wed, 29 Jan 2020 14:43:36 GMT

@novinosrin wrote:

proc summary nway data=have missing; 
 output  out = want(drop=_type_ _freq_) 
 idgroup(out[&obs](SingleRate--MarriedBracket)=) ;
run;

Oooh, PROC SUMMARY, I like it! I would not have thought of doing it this way.

But isn't there a limitation here? If &OBS>100, then according to the documentation this doesn't work (I have never tried it).

Re: How may I "transpose" a dataset so that it goes from multi-row, multi-column, to wide

novinosrin — Wed, 29 Jan 2020 15:03:12 GMT

Sir @PaigeMiller That's what makes you a genius and others ordinary. Great catch. Hmm, @AllSoEasy better to stick to the 1st solution modified for BY groups

data temp;
 set have;
 by byvar;
 if first.byvar then n=0;
 n+1;
 array 	t SingleRate--MarriedBracket;
 do over t;
  vn=put(vname(t),32.);
  v=t;
 output;
 end;
 keep byvar vn v n ;
run;

proc transpose data=temp out=want(drop=_:) delimiter=_;
by byvar ;
id  vn n;
var v;
run;

Re: How may I "transpose" a dataset so that it goes from multi-row, multi-column, to wide

novinosrin — Wed, 29 Jan 2020 15:15:20 GMT

Hi again @AllSoEasy The following meets the order of variables the way shown in your expected output

data have;
input (byVar SingleRate	SingleBracket	MarriedRate	MarriedBracket) (:$10.);
cards;
Group1	10%	$0	10%	$0
Group1	12%	$13,851	12%	$9,701
Group1	22%	$52,851	22%	$39,476
Group1	24%	$84,201	24%	$84,201
Group1	32%	$160,701	32%	$160,726
Group1	35%	$204,101	35%	$204,101
Group1	37%	$510,301	37%	$306,176
Group2	10%	$3,000	10%	$3,200
Group2	12%	$15,851	12%	$11,701
Group2	22%	$54,851	22%	$41,476
Group2	24%	$86,201	24%	$86,201
Group2	32%	$162,701	32%	$162,726
Group2	35%	$206,101	35%	$206,101
Group2	37%	$512,301	37%	$308,176
;

data temp;
 set have;
 by byvar;
 if first.byvar then n=0;
 n+1;
 array 	t SingleRate--MarriedBracket;
 do over t;
  n1=_i_;
  vn=put(vname(t),32.);
  v=t;
 output;
 end;
 keep byvar vn v n:;
run;

proc sort data=temp out=_temp;
by byvar n1;
run;

proc transpose data=_temp out=want(drop=_:) delimiter=_;
by byvar ;
id  vn n;
var v;
run;

Re: How may I "transpose" a dataset so that it goes from multi-row, multi-column, to wide

AllSoEasy — Wed, 29 Jan 2020 17:55:09 GMT

This is perfect! Thank you so much!!