如何用transpose 来转换数据格式

shiyiming

我的数据格式原本是这样：
id services quantity totalcosts
1  A  3   70
1  B  9   140
1  C  1   5
2  B  4   30
2  C  3   23
3  A  2   40
3  C  2   49

想用transpose 来转换下列数据格式：
id service_A quantity_A totalcosts_A service_B quantity_B totalcosts_B service_C quantity_C totalcosts_C

1  1 (注：1 表示有用过这项服务，0 表示没有用过这项服务） 3   70   1  9  140  1  1  5

2  0  0  0    1  4  30  1  3  23   
3  1  2  40  0  0  0    1  2  49

谢谢

shiyiming

[code:da39d]data ahuige;
  input id services$ quantity totalcosts ;
  cards;
1 A 3 70
1 B 9 140
1 C 1 5
2 B 4 30
2 C 3 23
3 A 2 40
3 C 2 49
;
run;

proc sort ;
  by id ;
run;

data final(drop=quantity totalcosts services);
  set ahuige;
  by id;
  array temp service_A quantity_A totalcosts_A service_B quantity_B totalcosts_B service_C quantity_C totalcosts_C ;
  array quant(65:67) quantity_A quantity_B quantity_C(0,0,0) ;
  array totalc(65:67) totalcosts_A totalcosts_B totalcosts_C(0,0,0);
  array serv(65:67) service_A service_B service_C(0,0,0);
  if first.id then
    do over temp;
      temp=0;
    end;
  quant(rank(services))=quantity;
  totalc(rank(services))=totalcosts;
  serv(rank(services))=1;
  if last.id then output;
run;

proc print;run;[/code:da39d]
我手持钢鞭将你打

shiyiming

Very useful and doable. thanks

shiyiming

Hi there, bother you again.
your SAS codes are quite complicated. Where can I get the detailed explanation for each step?

thanks.

shiyiming

actually.
I do not think it is complicated.
if you really want to use a word to describe it I suggest you to use 'Straight'.

1. [quote:21f1a]rank(services)[/quote:21f1a] is used to determine which variable you should pass values to.
2. [quote:21f1a]if last.id then output; [/quote:21f1a] is to output record in a batch.
3.  [quote:21f1a]do over temp[/quote:21f1a] is to eliminate the previous existing values in the last group.

shiyiming

thank you very much. I am just a newuser of SAS so...

Could I trouble you to explain why you add (0,0,0) for each of array except the first one (i.e. temp.), thanks

shiyiming

refer to the SAS help of ARRAY and the default value

shiyiming

Hi Thank you very much for your help and useful information.