Date:         Tue, 3 Feb 1998 09:56:01 +0100
Reply-To:     Denis Patrick <Patrick.Denis@ELECTRABEL.BE>
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         Denis Patrick <Patrick.Denis@ELECTRABEL.BE>
Subject:      Dataset conversion problem
Content-Type: text/plain; charset="us-ascii"

Dear Sas-L, 
 
I have a problem to convert a SAS dataset:
 
The input looks like this :
 
client  type    consom.	
100     1       1234	
100     2       123	
101     1       234	
102     1       235	
103     2       251	
104     3       125	
104     4       258	
105     1       56	
106     2       856	
107     4       523	
107     5       645	
108     2       127	
109     3       268	
109     5       259	
109     7       423	
109     8       159	
110     5       357	
….    ….    …	
 
And I want an output table like this :
 
client  T1      T2      T3      T4      T5      T7      T8      …     Tn+1	
100     1234    123                                                     	
101     234                                                             	
102     235                                                             	
103             251                                                     	
104                     125     258                                     	
105     56                                                              	
106             856                                                     	
107                             523     645                             	
108             127                                                     	
109                     268             259     423     159             	
110                                     357                             	
…     …     …     …     …     …     …     …     …     …	
 
The Type data in the 1st dataset will become variables Tx in the 2nd
dataset.
A client can have multiple types.
In the 2nd table, there is only 1 observation per client.
 
I have tried it by creating temporary macrovariabels
always re-reading the input dataset with the point= option.
 
%macro a1 ;
   data  temp0 ;
      set  sasuser.markin (keep= client  type  consom) ;
   run  ;
   proc sql
      noprint
      ;
      select count(type) into :nbtype
      from temp0 ;
   quit ;
   %do i = 1 %to &nbtype ;
   %if &i = 1
   %then
       %do ;
           data  _null_  ;
               iw = &i ;
               set  temp0  point = iw ;
               call symput( "typef", "T"||trim(left(type)) );
               stop ;
           run  ;
           %put  &typef ;
           data  temp1 ;
               iw = &i ;
               set  temp0  point = iw ;
               format  &typef  10.2 ;
               &typef = consom ;
               output ;
               stop ;
           run ;
           data  temp10 ;
               set  temp1  (drop= consom  type )
                   ;
           run  ;
       %end ;
   %else
       %do ;
           data  _null_  ;
               iw = &i ;
               set  temp0  point = iw ;
               call symput( "typef", "T"||trim(left(type)) );
               stop ;
           run  ;
           %put  &typef ;
           data  temp1 ;
               iw = &i ;
               set  temp0  point = iw ;
               format  &typef  10.2 ;
               &typef = consom ;
               output ;
               stop ;
           run  ;
           data  temp10 ;
               merge
                   temp10
                   temp1   (drop= consom  type )
                   ;
               by
                   client
                   ;
           run  ;
       %end ;
   %end ;
data  sasuser.markout;
   set  temp10 ;
run ;
%mend  a1 ;
%A1  ;
 
It works for a small input dataset but not for my production data
(500000 observations).
 
Can anyone help me.
 
Thanks.
Patrick Denis
Electrabel
Brussels-Belgium
Phone  : 02/518.62.41
Email : Patrick.Denis@Electrabel.be