SAS QUERY

[rakeshvvv]

Hi,

Can some one help me the the following query. I have data set with 2 Variables ID and VISIT..

100010001	SAFETY FOLLOW UP
100010001	SCREENING
100010001	EOT
100010001	C1D1
100010001	C1D2
100010001	C1D3
100010001	C1D4
100010001	C1D5

I should create an output in such a way that there should be only record for ID and new variable 'X should have all the values visit separated by ','

100010001    SAFETY FOLLOW UP,SCREENING,EOT,C1D1,C1D2,C1D3,C1D4,C1D5

THANKS IN ADVANCE

RAKESH

[Tom · Accepted solution]

Here is one way using "DOW" loop.  That is by placing the SET statement inside of the DO loop.

data want ;

  do until (last.id);

     set have ;

     by id ;

     length X $200 ;

     x=catx(',',x,visit);

  end;

  keep id x ;

run;

[Jagadishkatam]

Please try

proc transpose data=have out=trans;

by id;

var visit;

run;

data want;

length x $ 100;

set trans;

x=catx(',', of col:);

keep id x;

run;

Thanks,
Jag

[Astounding]

SQL isn't my strong point, but it seems like the most straightforward solution:

proc sql noprint;

   create table want as select id, strip(visit) separated by ', ' as x

   group by id

   from have;

run;

Might need tweaking for syntax, but that's the idea anyway.

[Ksharp]

Astounding,

You mixed up with statement creating a macro variable in SQL . You can not use SEPARATE except you are creating a macro variable.

But I hope one day, SAS SQL could have such power like your code . It is very intuitive .

data have;

infile cards expandtabs truncover;

input a : $20. b & $40.;

cards;

100010001 SAFETY FOLLOW UP

100010001 SCREENING

100010001 EOT

100010001 C1D1

100010001 C1D2

100010001 C1D3

100010001 C1D4

100010001 C1D5

;

run;

data want;

set have;

by a;

length x $ 2000;

retain x;

x=catx(',',x,b);

if last.a then do;output;call missing(x);end;

run;

Xia Keshan