how to transpose this

[odmhx]

I have the following data that I want to transpose, but tried and failed using proc transpose.

input data:
ProvID Company Rate Payment
1234 CompA 20.2 100.00
1234 CompA 30.5 200.00
1234 CompA 40.7 300.00
1234 CompB 20.2 150.00
1234 CompB 50.5 250.00

Intended output:

ProvID Company Rate1 Payment1 Rate2 Payment2 Rate3 Payment3
1234 CompA 20.2 100.00 30.5 200.00 40.7 300.00
1234 CompB 20.2 150.00 50.5 250.00

Can you help?

[LAP]

There two way to do this.

Using proc transpose

proc transpose data=test out=trans;
by PROVID Company;
var Rate Payment;
run;

Data final;
drop _name_;
merge trans (where=(_name_ = "Rate")
rename = (col1 = Rate1
col2=Rate2 col3 = Rate3))
trans (where=(_name_ = "Payment")
rename = (col1 = Payment1
col2=Payment2
col3 = Payment3));
by provID company;
run;


Alternately, you could do this in a data step. I usually do this when there are a lot of rename statements.

proc sort data=example;
data alternate (keep = provID company rate1-rate3 payment1-payment3);
array r(*) rate1 - rate3;
array p(*) payment1-payment3;

do i = 1 to 3;
set example;
by provID company;
r(i) = rate;
p(i) = payment;
if last.company then return;
end;
run;

[odmhx]

Thank you sooooooo much!

[scmebu]

Another way...

data foo;
length Company $ 20;
input ProvID Company Rate Payment;
cards;
1234 CompA 20.2 100.00
1234 CompA 30.5 200.00
1234 CompA 40.7 300.00
1234 CompB 20.2 150.00
1234 CompB 50.5 250.00
;
run;

/*
proc sort data=foo;
by ProvID Company;
run;
*/

data foo;
set foo;

by ProvID Company;

if first.Company
then i = 1;
else i = i + 1;

retain i;
run;

proc transpose data=foo out=bar1 PREFIX=RATE;
by ProvID Company;
id i;
var Rate;
run;

proc transpose data=foo out=bar2 PREFIX=PAYMENT;
by ProvID Company;
id i;
var Payment;
run;

data bar;
merge bar1(drop=_NAME_) bar2(drop=_NAME_);
by ProvID Company;
run;

proc print data=bar;
run;

[odmhx]

Thank you soooooo much!

[art297]

While what you want CAN be done with proc transpose, I typically prefer using proc summary to accomplish the task. You can avoid the proc sql code, below, if you know that maximum number of rate/payments that you'll confront. It is only included to count them:


data have;
input ProvID Company $ Rate Payment;
cards;
1234 CompA 20.2 100.00
1234 CompA 30.5 200.00
1234 CompA 40.7 300.00
1234 CompB 20.2 150.00
1234 CompB 50.5 250.00
;
run;

* Get magic number;
proc sql noprint;
select max(obs) into :obs
from (select count(*) as obs
from have
group by ProvID,Company)
;
quit;

* transpose to wide;
proc summary nway data=have missing;
class ProvID Company;
output
out = want(drop=_type_ _freq_)
idgroup(out[&obs](Rate Payment)=)
;
run;

HTH,
Art

[Ksharp]

WOW.Art T
I bet that Art Carpenter will definitely like your code.
My code is only another choice.

[pre]
data have;
input ProvID Company $ Rate Payment;
cards;
1234 CompA 20.2 100.00
1234 CompA 30.5 200.00
1234 CompA 40.7 300.00
1234 CompB 20.2 150.00
1234 CompB 50.5 250.00
;
run;
* Get magic number;
proc freq data=have noprint order=freq ;
tables provid*company /list out=count ;
run;
data _null_;
set count;
if _n_ = 1 then do; call symputx('count',count); stop; end;
run;
* transpose to wide;
proc sort data=have;
by provid company;
run;
data result(drop=rate payment i);
set have;
by provid company;
array r{*} rate1-rate&count;
array p{*} payment1-payment&count;
retain rate: payment:;
i+1;
r{i} = rate; p{i} =payment;
if last.company then do;
output;
i=0;
call missing(of r{*} p{*});
end;
run;
proc print ;run;
[/pre]



Ksharp

[art297]

KSharp,

I can't take credit for the idea. It was proposed by John King and Mike Zdeb in one of the papers presented at SGF last year. see:

http://support.sas.com/resources/papers/proceedings10/102-2010.pdf

Art

[Ksharp]

Hi.Art T.
It looks like that it is time for me to find more in documentation.


Ksharp

[odmhx]

Thank you sooooo much!

[odmhx]

Thank you soooooo much!!!

[deleted_user]

hello,

although my solution combines parts of solutions already offered, i write it for
the sake of time spent in finding it:

proc sort data=in;
by ProvID company;
run;

proc sql noprint;
select max(nb) into :c from (select count(company) as nb from in
group by provid,company);
quit;

data final;

do i=1 to &c;
set in (rename=(rate=i_rate payment=i_payment));
by company notsorted;

array rate {&c} ;
array payment {&c} ;

rate{i}=i_rate;
payment{i}=i_payment;

if last.company then do;
output;
leave;
end;

drop i_rate i_payment i;

end;

run;

Marius

[odmhx]

Thank you ALL, so so much! I like the codes from everyone and I'll save them all to digest and use not only in this program, but also in the future! Again, milions thanks to you all!