Date: Tue, 3 Feb 1998 10:46:50 -0500
Reply-To: "Nessle-Buck, Wren" <WNBuck@ITR-INC.COM>
Sender: "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From: "Nessle-Buck, Wren" <WNBuck@ITR-INC.COM>
Subject: Re: Dataset conversion problem
Content-Type: text/plain; charset="us-ascii"
Read up on proc transpose. I think it has what you need (and will be
much easier to maintain.). Maybe something like:
proc transpose data=markin out=markout prefix=T;
by client;
id type;
var consom;
Wren Nessle Buck
International Trade Resources
1001 G Street NW, Suite 1050
Washington DC 20001
(p) 202 383 3740
>-----Original Message-----
>From: Denis Patrick [SMTP:Patrick.Denis@ELECTRABEL.BE]
>Sent: Tuesday, February 03, 1998 1:56 AM
>To: SAS-L@UGA.CC.UGA.EDU
>Subject: Dataset conversion problem
>
>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
| 
