On Fri, 6 Jun 2008 17:32:42 -0400, Nat Wooding <Nathaniel.Wooding@DOM.COM> wrote:

>Tom > >I have a partial solution to your query but I do not have time to finish >the details this afternoon -- I am a half hour late leaving already. I >will try to finish this over the weekend if I get a quiet moment. > >Here is the code so far. > >Data start; >informat serial CARDIOBS $20. CARDIOWS $20. CNSBS $20. >CNSWS $20.; >input serial CARDIOBS CARDIOWS CNSBS CNSWS ; >cards; >1001 book/fashion book/fashion/house gyne/picko/car >gune/picko >1002 book fashion/house none picko/rule >1004 none house gyne/picko/car not done >run; >Proc Transpose out = trans; > var c: ; > by serial; >run; >* now split up the various values that have been transposed; >Data trans; > set trans; > LENGTH DETAILS $ 20;* THIS MAY NEED TO BE LONGER; > items = length( col1 ) - length( compress( col1 , '/' )) + 1; > if _name_ =:'CARD' THEN TYPE = 'CARDIO'; > ELSE if _name_ =:'CNS' THEN TYPE = 'CNS'; > * WHAT ARE THE LAST TWO LETTERS OF THE ORIGINAL VARIABLE NAME. THIS >IS ONE WAY TO FIND > OUT; > RETAIN FOUND 'Yes '; > END = REVERSE( SUBSTR( LEFT( REVERSE( _NAME_ )) , 1 , 2)); > do i = 1 to items; > string = scan( col1 , i , '/' ); > DETAILS = COMPRESS( TYPE || STRING ); > output; > end; >run; > >/* at this point, contatenate a set of obs for the nones and maybe not >dones and get rid of these >when there are yeses and nos */ >* NOW TRANSPOSE INTO THE FINAL TABLE; >PROC SORT DATA = TRANS; > BY SERIAL details; >RUN; > >Data Cleanup;* this piece is not working and needs to be replaced with the >obs mentioned > * above; > set trans; > by serial details; > if index(details, 'none') then found='none'; > if first.details = 1 and last.details = 1 then do; > output; > found='not done'; > if type = 'BS' then do; > type = 'WS'; > output; > end; > else do; > type = 'BS'; > output; > end; > end; >run; >PROC SORT DATA = cleanup;* since we have added obs, we need another sort; > BY SERIAL details; >RUN; > >PROC TRANSPOSE OUT = FINAL (DROP = _NAME_ ) DATA = cleanup; > VAR FOUND; > ID END; > BY SERIAL details; >RUN; > > > >proc print data = cleanup; >run; > >Nat Wooding >Environmental Specialist III >Dominion, Environmental Biology >4111 Castlewood Rd >Richmond, VA 23234 >Phone:804-271-5313, Fax: 804-271-2977 > > > > Tom Smith > <need_sas_help@YA > HOO.COM> To > Sent by: "SAS(r) SAS-L@LISTSERV.UGA.EDU > Discussion" cc > <SAS-L@LISTSERV.U > GA.EDU> Subject > A Complex Transpose/ SQL - could > not do it any short way. Need > 06/06/2008 04:02 help. PLEASE > PM > > > Please respond to > Tom Smith > <need_sas_help@YA > HOO.COM> > > > > > > >I have a follwoing dataset (with variabiles: serial, CARDIOBS, cARDIOWS, >CNSBS, CNSWS) > >serial CARDIOBS CARDIOWS CNSBS CNSWS >------------------------------------------------------------------------- > >1001 book/fashion book/fashion gyne/picko gune/picko > /house /car >1002 book fashion/house none picko/rule >1004 none house gyne/picko not done > /car > >Need to transpose variables ( or uing SQL ) as below and three new >variables >will also be created (serial, details, BS, WS): > > > >serial details BS WS >------------------------------------------------- >1001 CARDIO > book yes yes > fashion yes yes > house no yes > CNS > gyne yes yes > picko yes yes > car yes no > rule no no > >1002 CARDIO > book yes no > fashion no yes > house no yes > CNS > gyne none no > picko none yes > car none no > rule none yes > >1004 CARDIO > book none no > fashion none no > house none yes > CNS > gyne yes not done > picko yes not done > car yes not done > rule no not done > >Thank you very much. Please help me.

Hi NAT.. I will be waiting for your detail help. Hope you will have a wonderful weekend hand will have some time to look the problem Thanks