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
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;
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;
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;
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.
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;
@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).
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;
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;
This is perfect! Thank you so much!!
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!
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.
Ready to level-up your skills? Choose your own adventure.