BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AllSoEasy
Obsidian | Level 7

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

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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;

View solution in original post

8 REPLIES 8
novinosrin
Tourmaline | Level 20

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
Tourmaline | Level 20

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
Obsidian | Level 7

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
Tourmaline | Level 20

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
Diamond | Level 26

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

--
Paige Miller
novinosrin
Tourmaline | Level 20

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
Tourmaline | Level 20

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;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 1908 views
  • 5 likes
  • 3 in conversation