[prev in list] [next in list] [prev in thread] [next in thread]
List: sas-l
Subject: Re: How can I combine several variables into one
From: Ian Whitlock <iw1junk () COMCAST ! NET>
Date: 2006-11-30 17:44:15
[Download RAW message or body]
Summary: Transpose solution
#iw-value=1
Ethan,
Not necessarily better, but the techniques involved are important
and I think the code simpler (hence more likely to be correct)
albeit less efficient (by micro seconds unless data volume is
sufficiently large).
data w ; input
id (var1 var2 var3) ($) time $ ;
seq + 1 ;
cards ;
1 a . . t1
1 b . . t1
1 . . c t3
2 b . . t2
2 . b . t2
2 . c . t3
2 . a . t3
2 a . . t1
3 b . . t3
3 . . f t3
4 a . . t1
4 c . . t1
4 b . . t1
;
proc sort data = w ;
by id time seq ;
run ;
proc transpose data = w out = t ;
by id time seq ;
var v: ;
run ;
data wanted ( keep = id time list ) ;
retain id time list ; /* order vars */
length list $ 50 ;
set t ( where = ( col1 ^= " " ) ) ;
by id time ;
if first.time then list = col1 ;
else
do ;
if not ( index(list,trim(col1)) ) then
list = trim(list) || "," || col1 ;
end ;
if last.time ;
run ;
It is unclear what order you want the a, b and c listed.
You may want another sort before the last step to get
them in another order.
Ian Whitlock
==============
Date: Wed, 29 Nov 2006 20:26:28 -0800
Reply-To: Ethan <ethan06460@YAHOO.COM>
Sender: "SAS(r) Discussion"
From: Ethan <ethan06460@YAHOO.COM>
Organization: http://groups.google.com
Subject: Re: How can I combine several variables into one
Comments: To: sas-l
In-Reply-To:
<1164852477.468436.228700@14g2000cws.googlegroups.com>
Content-Type: text/plain; charset="us-ascii"
Hi all,
is there any way I can create a multiple dimension arrays to
combine
these variables into one without overwrite time variable? thanks
Actually my dataset should looks like this one.
id var1 var2 var3 time
1 a t1
1 b t1
1 c t3
2 b t2
2 b t2
2 c t3
2 a t3
2 a t1
3 b t3
3 f t3
4 a t1
4 c t1
4 b t1
my output should looks like:
id newvar time
1 a,b t1
1 b t3
1 b t2
2 c,a t3
2 b t3
3 f t3
4 a,c,b t1
> toby dunn wrote:
> > Well just to make up for my very bad misreading of teh
question a one data
> > step solution:
> >
> > data mydata;
> > id=1; var1='a'; var2='a'; var3='c'; output;
> > id=1; var1='b'; var2='a'; var3=''; output;
> > id=1; var1='a'; var2=''; var3=''; output;
> > id=2; var1='b'; var2='c'; var3=''; output;
> > id=2; var1=''; var2='b'; var3=''; output;
> > id=2; var1='a'; var2='c'; var3='g'; output;
> > id=2; var1='a'; var2='b'; var3=''; output;
> > id=3; var1='b'; var2=''; var3=''; output;
> > id=3; var1='e'; var2=''; var3='f'; output;
> > id=4; var1='a'; var2=''; var3=''; output;
> > run;
> >
> >
> > Data Need ( Keep = Id MyVar ) ;
> > Set MyData ;
> > By Id ;
> > Array VarZ ( 3 ) $ Var1-Var3 ;
> > Array NewVar ( 3 ) $20. ;
> > Retain NewVar: ;
> >
> >
> > Do J = 1 To Dim( NewVar ) ;
> > If First.Id Then NewVar( J ) = '' ;
> > End ;
> >
> >
> > Do I = 1 To Dim( VarZ ) ;
> >
> > If Not Missing( VarZ( I ) ) Then Do ;
> > NewVar( I ) = TranWrd( Compbl( NewVar( I ) || ' ' ||
VarZ( I ) ) , ' '
> > , ',' ) ;
> > End ;
> >
> > End ;
> >
> >
> > If Last.Id Then Do ;
> > Do K = 1 To Dim( NewVar ) ;
> > MyVar = Substr( NewVar( K ) , 2 ) ;
> > If MyVar Ne '' Then Output ;
> > End ;
> > End ;
> > Run ;
> >
> > Proc Print
> > Data = Need ;
> > Run ;
> >
> >
> >
> > Toby Dunn
> >
> > Quickly, bring me a beaker of wine, so that I may wet my mind
and say
> > something clever.
> > Aristophanes
> >
> > Wise people, even though all laws were abolished, would still
lead the same
> > life.
> > Aristophanes
> >
> > You should not decide until you have heard what both have to
say.
> > Aristophanes
> >
> >
> >
> >
> >
> >
> > From: toby dunn <tobydunn@HOTMAIL.COM>
> > Reply-To: toby dunn <tobydunn@HOTMAIL.COM>
> > To: SAS-L
> > Subject: Re: How can I combine several variables into one
> > Date: Wed, 29 Nov 2006 21:30:29 +0000
> >
> > You know I think your right....hmmm eye site must be getting
bad in my young
> > age....
> >
> >
> >
> > Toby Dunn
> >
> > Quickly, bring me a beaker of wine, so that I may wet my mind
and say
> > something clever.
> > Aristophanes
> >
> > Wise people, even though all laws were abolished, would still
lead the same
> > life.
> > Aristophanes
> >
> > You should not decide until you have heard what both have to
say.
> > Aristophanes
> >
> >
> >
> >
> >
> >
> > From: "Jake Bee" <johbee@gmail.com>
> > To: "toby dunn" <tobydunn@hotmail.com>
> > CC: SAS-L
> > Subject: Re: How can I combine several variables into one
> > Date: Wed, 29 Nov 2006 16:28:22 -0500
> >
> > I haven't followed this thread closely, but it seem the
author want to
> > concat, the vars by id by rows and not columns. Therefore,
the following
> > suggestion:
> >
> > dm 'log' clear;
> > dm 'out' clear;
> >
> > data mydata;
> > id=1; var1='a'; var2='a'; var3='c'; output;
> > id=1; var1='b'; var2='a'; var3=''; output;
> > id=1; var1='a'; var2=''; var3=''; output;
> > id=2; var1='b'; var2='c'; var3=''; output;
> > id=2; var1=''; var2='b'; var3=''; output;
> > id=2; var1='a'; var2='c'; var3='g'; output;
> > id=2; var1='a'; var2='b'; var3=''; output;
> > id=3; var1='b'; var2=''; var3=''; output;
> > id=3; var1='e'; var2=''; var3='f'; output;
> > id=4; var1='a'; var2=''; var3=''; output;
> > run;
> >
> > data mydata;
> > set mydata;
> > rowid=_N_;
> > run;
> >
> > proc sort data=mydata;
> > by id;
> > run;
> >
> > proc transpose data=mydata out=t_mydata let;
> > var var1 var2 var3;
> > id rowid;
> > by id;
> > run;
> >
> > proc sort data=t_mydata;
> > by id _name_;
> > run;
> >
> > proc contents data=t_mydata noprint out=c_mydata(keep=name);
> > run;
> >
> > data c_mydata2;
> > set c_mydata(where=(upcase(name) ^in ('_NAME_','ID')));
> > format val 3.;
> > val=input(translate(name,' ','_'),3.);
> > run;
> >
> > proc sql noprint;
> > select max(val) into : dim_val from c_mydata2;
> > quit;
> >
> > %put &dim_val;
> >
> > %macro _newvar_;
> > data final(keep=id newvar);
> > set t_mydata(drop=_name_);
> > length newvar $10;
> > %do i=1 %to &dim_val;
> > newvar=compbl(newvar||_&i);
> > %end;
> > len=length(newvar);
> > newvar=compbl(substr(newvar,1,len));
> > newvar=substr(translate(newvar,',',' '),1,len);
> > if substr(newvar,1,1)=',' then newvar=
substr(newvar,2);
> > run;
> >
> > proc sort data=final(where=(newvar ne ''));
> > by id;
> > run;
> >
> > proc print data=final;
> > run;
> > %mend _newvar_;
> >
> > %_newvar_;
> >
> >
> >
> >
> > On 11/29/06, toby dunn <tobydunn@hotmail.com> wrote:
> > >
> > >Or so is this just for giggles another V8 Solution ( I
havent tested it to
> > >work out any bugs) :
> > >
> > >Data Need ( Drop = I ) ;
> > >Set Have ;
> > >Array Var ( * ) Var: ;
> > >
> > >Do I = 1 To Dim( Var ) ;
> > > If ( I Ne Dim( Var ) ) Then Do ;
> > > NewVar = Compress( NewVar || Var( I ) || ',' ) ;
> > > End ;
> > > Else DO ;
> > > NewVar = Compress( NewVar || Var( I ) ) ;
> > > End ;
> > >End ;
> > >
> > >Run ;
> > >
> > >
> > >
> > >
> > >
> > >
> > >Toby Dunn
> > >
> > >Quickly, bring me a beaker of wine, so that I may wet my
mind and say
> > >something clever.
> > >Aristophanes
> > >
> > >Wise people, even though all laws were abolished, would
still lead the
> > >same
> > >life.
> > >Aristophanes
> > >
> > >You should not decide until you have heard what both have to
say.
> > >Aristophanes
> > >
> > >
> > >
> > >
> > >
> > >
> > >From: "Terjeson, Mark" <Mterjeson@RUSSELL.COM>
> > >Reply-To: "Terjeson, Mark" <Mterjeson@RUSSELL.COM>
> > >To: SAS-L
> > >Subject: Re: How can I combine several variables into one
> > >Date: Wed, 29 Nov 2006 12:04:55 -0800
> > >
> > >Hi Ethan,
> > >
> > >This solution is v8.2 compatible:
> > >
http://listserv.uga.edu/cgi-bin/wa?A2=ind0611E&L=sas-l&P=R4591
> > >
> > >
> > >
> > >Hope this is helpful.
> > >
> > >
> > >Mark Terjeson
> > >Senior Programmer Analyst, IM&R
> > >Russell Investment Group
> > >
> > >
> > >Russell
> > >Global Leaders in Multi-Manager Investing
> > >
> > >
> > >
> > >
> > >
> > >
> > >-----Original Message-----
> > >From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On
Behalf Of
> > >Ethan
> > >Sent: Wednesday, November 29, 2006 11:58 AM
> > >To: SAS-L
> > >Subject: How can I combine several variables into one
> > >
> > >Hi all,
> > >
> > >I forgot to mention I don't have access to SAS 9.0. I only
can access
> > >SAS8.2. Then what I can do without CATX function.
> > >
> > >Actually my dataset should looks like this one.
> > >
> > >id var1 var2 var3
> > >1 a a c
> > >1 b a
> > >1 a
> > >2 b c
> > >2 b
> > >2 a c g
> > >2 a b
> > >3 b
> > >3 e f
> > >4 a
> > >
> > >my output should looks like:
> > >
> > >id newvar
> > >1 a,b,a
> > >1 a,a
> > >1 c
> > >2 b,a,a
> > >2 b
> > >2 c,b,c
> > >2 g
> > >3 b,e
> > >3 f
> > >4 a
> > >
> > >Thanks for your help!
> > >
> > >
_________________________________________________________________
> > >Talk now to your Hotmail contacts with Windows Live
Messenger.
> > >
> > >
http://clk.atdmt.com/MSN/go/msnnkwme0020000001msn/direct/01/?href
=http://get.live.com/messenger/overview
> > >
> >
> >
_________________________________________________________________
> > All-in-one security and maintenance for your PC. Get a free
90-day trial!
> >
http://clk.atdmt.com/MSN/go/msnnkwlo0050000002msn/direct/01/?href
=
http://clk.atdmt.com/MSN/go/msnnkwlo0050000001msn/direct/01/?href
=http://www.windowsonecare.com/?sc_cid=msn_hotmail
> >
> >
_________________________________________________________________
> > Get the latest Windows Live Messenger 8.1 Beta version. Join
now.
> > http://ideas.live.com
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic