Hi,
i need to transpose 3 variables by 1 key
the only one way that i thought if to make 3 proc transpose and after merge the 3 data set togheter
but i think that exist something easier, can u help me?
this is an example of the data that i m managing
col1 col2 col3 col4
A 2014 N 0
A 2013 X 1
A 2012 N 0
A 2011 X 2
B 2013 X 5
B 2012 X 0
B 2011 N 1
B 2010 N 0
...
what i would create is :
A 2014 N 0 2013 X 1 2012 N 0 2011 X 2
B 2013 X 5 2012 X 0 2011 N 1 2010 N 0
...
thanks
Davide
Yep, transpose and merge. You could also do it with retained arrays and output last from group:
data have;
attrib col1 col2 col3 col4 format=$20.;
infile datalines;
input col1 $ col2 $ col3 $ col4 $;
datalines;
A 2014 N 0
A 2013 X 1
A 2012 N 0
A 2011 X 2
B 2013 X 5
B 2012 X 0
B 2011 N 1
B 2010 N 0
;
run;
data want (drop=ind j;
set have;
array results{4,3} $200.; /* Assuming a 4*3 grid */
array dat{3} col2-col4;
by col1;
retain ind results1-results12;
if first.col1 then ind=1;
do j=1 to 3;
results{ind,j}=dat{j};
end;
ind=ind+1;
if last.col1 then output;
run;
Hello,
Assuming the data is sorted upon col1:
data have;
input col1 $ col2 col3 $ col4;
datalines;
A 2014 N 0
A 2013 X 1
A 2012 N 0
A 2011 X 2
B 2013 X 5
B 2012 X 0
B 2011 N 1
B 2010 N 0
;
run;
proc sql noprint;
select min(col2), max(col2)
into :miny, :maxy from have;
quit;
data want;
array vars2{&miny:&maxy} y%sysfunc(strip(&miny))-y%sysfunc(strip(&maxy));
array vars3{&miny:&maxy} $ z%sysfunc(strip(&miny))-z%sysfunc(strip(&maxy));
array vars4{&miny:&maxy} w%sysfunc(strip(&miny))-w%sysfunc(strip(&maxy));
do until (last.col1);
set have;
by col1;
vars2{col2}=col2;
vars3{col2}=col3;
vars4{col2}=col4;
end;
drop col2-col4;
run;
How about this :
data have; attrib col1 col2 col3 col4 format=$20.; infile datalines; input col1 $ col2 $ col3 $ col4 $; datalines; A 2014 N 0 A 2013 X 1 A 2012 N 0 A 2011 X 2 B 2013 X 5 B 2012 X 0 B 2011 N 1 B 2010 N 0 ; run; proc sql; create table temp as select b.*,col3,col4 from have a right join (select * from (select distinct col1 from have),(select distinct col2 from have) ) b on a.col1=b.col1 and a.col2=b.col2; quit; data temp; set temp; by col1; if first.col1 then n=0; n+1; run; data _null_; set temp(keep=col1 n) end=last; if _n_ eq 1 then call execute('data want(drop=n);merge '); call execute('temp(where=(col1="'||strip(col1)||'" and n='||strip(n)||') rename=(col2=col2'||strip(n)||' col3=col3'||strip(n)||' col4=col4'||strip(n)||'))'); if last then call execute(';by col1;run;'); run;
Xia Keshan
Thanks a lot
Davide
I'm a little late to the party, but here's another one. I'm not that good with arrays, so the fourth step can probably be tidied up.
Tom
data have;
input col1 $ col2 col3 $ col4;
cards;
A 2014 N 0
A 2013 X 1
A 2012 N 0
A 2011 X 2
B 2013 X 5
B 2012 X 0
B 2011 N 1
B 2010 N 0
run;
data inter1;
set have;
dataval = catx('09'x, put(col2,4.), col3, put(col4, 1.));
keep col1 col2 dataval;
run;
proc transpose data=inter1 out=inter2(drop=_name_) prefix=In;
by col1;
id col2;
var dataval;
run;
data want;
array InVars $ In2014 In2013 In2012 In2011 In2010;
array OutYears Yr2014 Yr2013 Yr2012 Yr2011 Yr2010;
array OutC3 $ C32014 C32013 C32012 C32011 C32010;
array OutC4 C42014 C42013 C42012 C42011 C42010;
set inter2;
do _i = 1 to 5;
OutYears(_i) = scan(InVars(_i), 1, '09'x);
OutC3(_i) = scan(InVars(_i), 2, '09'x);
OutC4(_i) = scan(InVars(_i), 3, '09'x);
end;
drop In2014-In2010 _i;
output;
run;
Did you know that you can have descending "Numbered Range Lists".
If you download the free macro at A Better Way to Flip (Transpose) a SAS Dataset - sasCommunity, here is a really easy line of code that will solve your problem:
%transpose(data=have, out=want, by=col1, delimiter=_, var=col2-col4)
Hi Arthur.T ,
Did you check the OP's output closely ? He asked a totally different layout :
A 2014 N 0 2013 X 1 2012 N 0 2011 X 2
B . 2013 X 5 2012 X 0 2011 N 1 2010 N 0
They are all in wrong place with its original positon .Sorry, I am afraid that our macro wouldn't work for this scenario .
Xia Keshan
: I missed the part about the variable ordering, but I still think it would work and be the easiest to understand. How about:
data order;
informat col2 $20.;
format col2 $20.;
input col2 order;
cards;
2014 1
2013 2
2011 3
2010 4
;
%transpose(data=have, out=want, by=col1, id=col2, delimiter=_,
var=col2-col4, preloadfmt=order)
Yeah. But you need to make a ORDER dataset firstly right ?
BTW, your colleague Peter.E is now in Beijing,China .
Xia Keshan
Hi Arthur.T ,
Sorry, I think again, Yours might be more simple and easy to understand for OP .
and more important thing is OP don't need to understand how these SAS code work, Macro is good for him .
Regards
Xia Keshan
: I made it even easier by adding a descendingid parameter to the macro. Now the OP can obtain the file by running:
%transpose(data=have, out=want, by=col1, id=col2, delimiter=_, var=col2-col4, descendingid=yes)
The updated macro is available at the usual place, namely A Better Way to Flip (Transpose) a SAS Dataset - sasCommunity. However, if you've already downloaded the macro from there, you'll probably have to refresh your browser screen after clicking on the link for the code and/or paper.
The preloadfmt parameter is still useful as it can deal with something like the following where you want direct control over the ordering of id values. In the case below I wanted them in the Tom, Dick, Harry order :
data have;
informat name $5.;
format name $5.;
input year name height weight;
cards;
2013 Dick 6.1 185
2013 Tom 5.8 163
2013 Harry 6.0 175
2014 Dick 6.1 180
2014 Tom 5.8 160
2014 Harry 6.0 195
;
data order;
informat name $5.;
format name $5.;
input name order;
cards;
Tom 1
Dick 2
Harry 3
;
%transpose(data=have, out=want, by=year, id=name,
delimiter=_, var=height weight, var_first=no, preloadfmt=order)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.