Data Manipulation with 1

[Asender]

Hi folks.

I frequently deal with datasets in the folling format:

OBS POLNO UNITS PRICE FUNDNAME VALUE

1 K1 100 5 MANAGED 500
2 K1 200 10 EQUITY 2000
3 K1 10 1 WITH PR 10

I need to manipulate this data set so that all this data
appears in one observation under the POLNO.

As it stands at the moment I can accomplish this by running a proc transpose on each on the variables, (UNITS -- VALUE), creating new unique variable names in the process before merging the 4 new datasets together.

I was wondering if there is a more efficient way of accomplishing this task? Can proc tranpose do this in one step?

Regards,



Chris....

 

[teralearner]

Here is how I would do it;

data tst;
input POLNO $2. UNITS PRICE VALUE FUNDNAME & $8. ;
datalines;
K1 100 5 500 MANAGED
K1 200 10 2000 EQUITY
K1 10 1 10 WITH PR
;

proc sort data = tst;
by POLNO;
run;

proc freq data = tst NOPRINT;
table polno / out = polno;
run;

proc sql noprint;
select max(COUNT) into: num
from polno;
quit;

%put num = #

proc transpose data = tst out = tst_tran;
by polno;
var UNITS PRICE VALUE FUNDNAME;
run;

%macro set (varname);
set tst_tran (where = (_NAME_ = "&varname&quot rename = (
%do i = 1 %to #
col&i = &varname&i
%end;
) );
%mend set;

data tran;
%set(UNITS);
%set(PRICE);
%set(VALUE);
%set(FUNDNAME);
DROP _NAME_;
run;