BookmarkSubscribeRSS Feed
odmhx
Calcite | Level 5
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?
12 REPLIES 12
LAP
Quartz | Level 8 LAP
Quartz | Level 8
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
Calcite | Level 5
Thank you sooooooo much!
scmebu
SAS Employee
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
Calcite | Level 5
Thank you soooooo much!
art297
Opal | Level 21
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
Super User
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
Opal | Level 21
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
Super User
Hi.Art T.
It looks like that it is time for me to find more in documentation.


Ksharp
odmhx
Calcite | Level 5
Thank you sooooo much!
odmhx
Calcite | Level 5
Thank you soooooo much!!!
deleted_user
Not applicable
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
Calcite | Level 5
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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 1442 views
  • 0 likes
  • 6 in conversation