topic Re: how to transpose this in SAS Procedures

how to transpose this

odmhx — Thu, 06 Jan 2011 21:18:45 GMT

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?

Re: how to transpose this

LAP — Thu, 06 Jan 2011 22:48:59 GMT

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;

Re: how to transpose this

scmebu — Thu, 06 Jan 2011 23:03:12 GMT

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;

Re: how to transpose this

art297 — Thu, 06 Jan 2011 23:29:34 GMT

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

Re: how to transpose this

Ksharp — Fri, 07 Jan 2011 05:22:45 GMT

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

Re: how to transpose this

deleted_user — Fri, 07 Jan 2011 10:14:47 GMT

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

Re: how to transpose this

art297 — Fri, 07 Jan 2011 13:40:23 GMT

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

Re: how to transpose this

odmhx — Fri, 07 Jan 2011 13:48:01 GMT

Thank you sooooooo much!

Re: how to transpose this

odmhx — Fri, 07 Jan 2011 13:48:32 GMT

Thank you soooooo much!

Re: how to transpose this

odmhx — Fri, 07 Jan 2011 13:48:59 GMT

Thank you soooooo much!!!

Re: how to transpose this

odmhx — Fri, 07 Jan 2011 13:49:29 GMT

Thank you sooooo much!

Re: how to transpose this

odmhx — Fri, 07 Jan 2011 13:50:47 GMT

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!

Re: how to transpose this

Ksharp — Mon, 10 Jan 2011 01:09:55 GMT

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

Ksharp