Help using Base SAS procedures

how to transpose this

Reply
Contributor
Posts: 35

how to transpose this

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?
Frequent Contributor
Frequent Contributor
Posts: 76

Re: how to transpose this

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;
Contributor
Posts: 35

Re: how to transpose this

Thank you sooooooo much!
SAS Employee
Posts: 17

Re: how to transpose this

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;
Contributor
Posts: 35

Re: how to transpose this

Thank you soooooo much!
PROC Star
Posts: 7,417

Re: how to transpose this

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 Smiley Surprisedbs
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
Super User
Posts: 9,775

Re: how to transpose this

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
PROC Star
Posts: 7,417

Re: how to transpose this

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
Super User
Posts: 9,775

Re: how to transpose this

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


Ksharp
Contributor
Posts: 35

Re: how to transpose this

Thank you sooooo much!
Contributor
Posts: 35

Re: how to transpose this

Thank you soooooo much!!!
N/A
Posts: 0

Re: how to transpose this

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
Contributor
Posts: 35

Re: how to transpose this

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!
Ask a Question
Discussion stats
  • 12 replies
  • 240 views
  • 0 likes
  • 6 in conversation