create index in SAS using do loop

Say I have a set of data in this format:

ID  Product account open date  
1   A   20100101  
1   B   20100103  
2   C   20100104  
2   A   20100205  
2   D   20100605  
3   A   20100101  

And I want to create a column to capture the sequence of the products opened so the table will look like this:

ID  First   Second  third  
1   A   B   
2   C   A   D  
3   A       

I know I need to create an index for each ID so I can transpose the data afterwards:

ID  Product account open date   sequence  
1   A   20100101    1  
1   B   20100103    2  
2   C   20100104    1  
2   A   20100205    2  
2   D   20100605    3  
3   A   20100101    1 

From my limited knowledge in do loop, I think I need to write something like this:

if first.ID and not last.ID then n=1 do while ID not last n+1

Something like that. 开发者_开发知识库 Can anyone help me with the exact syntax? I have tried googling for similar codes and haven't had much luck.

Thanks!

I'd sort by ID and then date and use proc transpose for simplicity. Here's an example:

data prod;
input ID Product $ Open_DT :yymmdd8.;
format open_dt date9.;
datalines;
1 A 20100101
1 B 20100103
2 C 20100104
2 A 20100205
2 D 20100605
3 A 20100101
;
run;
proc sort data=prod;
  by ID Open_DT;run;
proc transpose data=prod 
  out=prod_trans(drop=_name_)
    prefix=ITEM;
  by id;
  var Product;
run;

proc print data=prod_trans noobs;
run;