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

[AllSoEasy]

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

[novinosrin]

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;

[novinosrin]

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;

[AllSoEasy]

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%	$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
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.

[novinosrin]

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;

[PaigeMiller]

---

@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).

[novinosrin]

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;

[novinosrin · Accepted solution]

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;

[AllSoEasy]

This is perfect! Thank you so much!!