topic Proc Transpose, can anyone help? in SAS Procedures

Proc Transpose, can anyone help?

toffee — Mon, 29 Mar 2010 15:37:47 GMT

Staff_id Product Pts_Earned
182715 EPE 150
182825 EASB 22200
182825 EIKPA 1056
182825 EKD 810
182825 EPE 2400
182825 ESM 30

From the above dataset, i got the following answer.

Staff_id EASB EIKPA EKD EPE ESM
182715 150
182825 22200 1056 810 2400 30

What happend to 182715, it supposed to be EPE, not EASB?

Can anyone help?

My code:
PROC TRANSPOSE DATA=I OUT=TEST(RENAME=(COL1=EASB COL2=EIKPA COL3=EKD COL4=EPE COL5=ESM)) NAME=PRODUCT;

BY STAFF_ID;

VAR PTS_EARNED;

LABEL PRODUCT = 'PRODUCT';

RUN;

Re: Proc Transpose, can anyone help?

sbb — Mon, 29 Mar 2010 15:44:38 GMT

You are not using the PROC correctly - don't presume that the incoming COLn is "sorted by SAS". Look to use additional functions to get the desired result.

Scott Barry
SBBWorks, Inc.

Re: Proc Transpose, can anyone help?

Deendayal — Mon, 29 Mar 2010 19:45:38 GMT

Hi,

If I get your requirement correctly, you can accomplish the desired task using arrays also.
Try with this code. check whether you are getting right output or not?

data dd;
infile datalines;
input Staff_id Product $ Pts_Earned;
datalines;
182715 EPE 150
182825 EASB 22200
182825 EIKPA 1056
182825 EKD 810
182825 EPE 2400
182825 ESM 30
;
data dds(drop=product pts_earned);
length staff_id 8.;
array prd{5} EASB EIKPA EKD EPE ESM;
set dd;
if product='EPE' then EPE=pts_earned;
if product='EASB' then EASB=pts_earned;
if product='EIKPA' then EIKPA=pts_earned;
if product='EKD' then EKD=pts_earned;
if product='ESM' then ESM=pts_earned;
proc print;
run;

Thanks,
Deendayal Cheni
Oracle Corporation (Formerly Sun Microsystems Inc.)

Re: Proc Transpose, can anyone help?

sbb — Mon, 29 Mar 2010 20:19:16 GMT

Stick with PROC TRANSPOSE (coded correctly), given the fewer moving parts (code lines) involved.

Scott Barry
SBBWorks, Inc.

Re: Proc Transpose, can anyone help?

deleted_user — Wed, 31 Mar 2010 11:21:27 GMT

Hi ,

You can try this piece of code . It will work correctly.

proc sort data=dd ;
by staff_id product;
run;

proc transpose data=dd out=dd_tran prefix=product;
by staff_id ;
var pts_earned;
id product;
run;