topic SAS QUERY in SAS Procedures

SAS QUERY

rakeshvvv — Mon, 01 Jun 2015 16:36:03 GMT

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

Re: SAS QUERY

Reeza — Mon, 01 Jun 2015 16:57:37 GMT

Re: SAS QUERY

Tom — Mon, 01 Jun 2015 16:57:50 GMT

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;

Re: SAS QUERY

Jagadishkatam — Mon, 01 Jun 2015 17:04:44 GMT

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

Re: SAS QUERY

Astounding — Mon, 01 Jun 2015 17:16:35 GMT

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.

Re: SAS QUERY

Ksharp — Tue, 02 Jun 2015 12:32:17 GMT

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